Add column with a table of a dynamic list of columns

[Anonymous]

Hi,

I am trying to tackle in a semi-dynamic manner the retreival of data from JSON files (with several levels of nesting for the data).

When 1st level of data is not a lateral I get columns which are Lists and Records.

I saw a cool tip on YouTube on combining list columns into a single added column and then expanding them all at one (Expand Multiple Lists Simultaneously in Power Query on Goodly Channel).

This row of code does exactly what I want but the column names here are "hard coded"

addListColumnsAsColumn = Table.AddColumn(relevantFirstLevel, "FirstLevelListsColumn", each Table.FromColumns({[name], [description], [externalId], [price], [overrideOption]})),

I have in my file a helper function which will return the list of relevant column name (in this case it will return this list of strings {"name", "description", "externalId", "price", "overrideOption"})

Any recommendation how I can, within the function statetement of adding a column (each), convert the list of column names to the list of values within those columns? 

[Anonymous · Accepted solution]

It took me some thought, time and some trial-and-error but eventually I managed to find a solution.

I created a helper function called listsToTableOfRecords which accepts the record and the list of names which we want the record items as a table or records (instead of Lists).

(inRecord as any, attributeNames as any) =>
let
Source = Record.SelectFields(inRecord, attributeNames),
numberOfAttributes = List.Count(attributeNames),

listOfColumns = List.Generate(
()=>0,
each _<numberOfAttributes, each _+1,
each Record.Field(Source, attributeNames{_})
),

Response = Table.FromColumns(listOfColumns, attributeNames)
in
Response

Instead of adding the column with the hard-coded columns, I called the new function.

= Table.AddColumn(relevantFirstLevel, "FirstLevelListsColumn", each listsToTableOfRecords(_, relevantListAttributes))

Hope it is useful to someone else in the future ...

Know of a simpler way, I'd love to learn.