How to get Column Names dynamically in Excel Power Query

Question

I am using Power Query in Excel to read JSON files. I have a sample working script, as follows:

let
    Source = Json.Document(File.Contents("E:\laureates.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode", "bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"}, {"Value.id", "Value.firstname", "Value.surname", "Value.born", "Value.died", "Value.bornCountry", "Value.bornCountryCode", "Value.bornCity", "Value.diedCountry", "Value.diedCountryCode", "Value.diedCity", "Value.gender", "Value.prizes"})
in
    #"Expanded Value1"

The second and third arguments of the expression for #"Expanded Value1" has the names of the columns hard-coded; this code is generated via the user interface.

I would like to reuse the script. The problem is that whenever the source file changes - has different column names or new column names - I encounter an error. A workaround is to regenerate the script.

I can avoid the issue if I can specify the second and third arguments as expressions that will be evaluated dynamically at runtime.

So far, my attempts have failed: I would appreciate any hints regarding how I can replace the second and third arguments as code or expressions.

Alexis Olson · Accepted Answer · 2020-04-05 17:09:42Z

2

I think you can use Record.FieldNames to generate that list dynamically.

Something like this:

Table.ExpandRecordColumn(
    #"Expanded Value",
    "Value",
    Record.FieldNames([Value]),
    List.Transform(Record.FieldNames([Value]), each "Value." & _)
)

Edit: As AAsk points out, the above syntax is incorrect since it's attempting to pull row context on a table-level operation. Instead of [Value] for each row, we need to use a representative one to be applied to the whole column and picking #"Expanded Value"{0}[Value], the record from the first row, should work.

Table.ExpandRecordColumn(
    #"Expanded Value",
    "Value",
    Record.FieldNames(#"Expanded Value"{0}[Value]),
    List.Transform(Record.FieldNames(#"Expanded Value"{0}[Value]), each "Value." & _)
)

The List.Transform is there to prepend "Value." to the start of each column name but it works just fine to use Record.FieldNames(#"Expanded Value"{0}[Value]) twice instead.

edited Apr 5, 2020 at 17:09

answered Apr 4, 2020 at 22:03

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AAsk Over a year ago

Thanks. Unfortunately, I am getting Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Alexis Olson Over a year ago

Yeah, I should have tested the syntax more carefully. I've edited my answer.

AAsk · Accepted Answer · 2020-04-05 06:50:26Z

0

Record.FieldNames in your (Alexis Olson) answer made me realise that I can get the column names from a single record (the logic being that all records should have the same column names). There the code that gets the column names dynamically is:

let
Source = Json.Document(File.Contents("E:\laureates.json")),
#"Converted to Table" = Record.ToTable(Source),

#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", 
Record.FieldNames(#"Expanded Value"{0}[Value]),
Record.FieldNames(#"Expanded Value"{0}[Value])
)

in #"Expanded Value1"

Now I can change the file name and its contents of files with different content are displayed correctly.

edited Apr 5, 2020 at 6:50

answered Apr 5, 2020 at 6:44

AAsk

1,4914 gold badges18 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to get Column Names dynamically in Excel Power Query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related