CODEWITHSUNDEEP
excelpowerquerym
AAsk
AAsk

Reputation: 1491

How to get Column Names dynamically in Excel Power Query

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.

Upvotes: 1

Views: 2844

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40244

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.

Upvotes: 2

AAsk
AAsk

Reputation: 1491

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.

Upvotes: 0

Related Questions