Forum Discussion
Dynamically expanding all columns with lists
I am processing an API response and there are numerous columns that contains a mixture of lists and nulls. Most (but not all) of these lists are only 1 item long.
The columns returns depend on the search query and I would like to dynamically extract the values from all the lists within all the columns (some columns have no lists). I also would like to extract the contents within the cell (i.e. not creating a row per item), so concatenating items where required.
I.e. first screenshot to second as shown below.
I have the code to dynamically expand all the columns within the record (step 1) but this is the next crucial step that having tested various things I am struggling with. Is anyone able to kindly help?
1st screenshot
2nd screenshot
Have worked out a way!
The step that does it is below. 'GetRecordFields' is a list of all distinct columns in every row of records (this varies by row and is expanded in the first step). I have also nested an if statement to check if the cell is a list because otherwise it produces an error for non list values.
List.Transform(GetRecordFields, each { _ , each if Value.Is(_, type list) = true then Text.Combine( _ , ",") else _}))Full code is below, which also has steps to dynamically change type to text and remove errors which is required for a data flow. The code in the link that jennratten provided is much more complex but the below should work if you don't have to flatten the json struture.
let Source = Json.Document(#"Parameter (3)", 65001), Navigation = Source[elementVOList], #"Converted to table" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error), GetRecordFields = let #"Added Record Fields" = Table.AddColumn(#"Converted to table", "ColumnNames", each Record.FieldNames([Column1])), #"Select ColNames" = Table.SelectColumns(#"Added Record Fields", {"ColumnNames"}), #"Expanded ColNames" = Table.ExpandListColumn(#"Select ColNames", "ColumnNames"), #"Removed Duplicates" = Table.Distinct(#"Expanded ColNames"), ColNames = #"Removed Duplicates"[ColumnNames] in ColNames, #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table", "Column1", GetRecordFields, GetRecordFields), #"Expanded lists" = Table.TransformColumns(#"Expanded Column1", List.Transform(GetRecordFields, each { _ , each if Value.Is(_, type list) = true then Text.Combine( _ , ",") else _})), #"Replaced errors" = Table.ReplaceErrorValues(#"Expanded lists", List.Transform(GetRecordFields, each {_, 0})), #"Changed Type" = Table.TransformColumnTypes(#"Replaced errors", List.Transform(GetRecordFields, each {_, type text})) in #"Changed Type"
2 Replies
Hello - this is an option you can try.
Have worked out a way!
The step that does it is below. 'GetRecordFields' is a list of all distinct columns in every row of records (this varies by row and is expanded in the first step). I have also nested an if statement to check if the cell is a list because otherwise it produces an error for non list values.
List.Transform(GetRecordFields, each { _ , each if Value.Is(_, type list) = true then Text.Combine( _ , ",") else _}))Full code is below, which also has steps to dynamically change type to text and remove errors which is required for a data flow. The code in the link that jennratten provided is much more complex but the below should work if you don't have to flatten the json struture.
let Source = Json.Document(#"Parameter (3)", 65001), Navigation = Source[elementVOList], #"Converted to table" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error), GetRecordFields = let #"Added Record Fields" = Table.AddColumn(#"Converted to table", "ColumnNames", each Record.FieldNames([Column1])), #"Select ColNames" = Table.SelectColumns(#"Added Record Fields", {"ColumnNames"}), #"Expanded ColNames" = Table.ExpandListColumn(#"Select ColNames", "ColumnNames"), #"Removed Duplicates" = Table.Distinct(#"Expanded ColNames"), ColNames = #"Removed Duplicates"[ColumnNames] in ColNames, #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table", "Column1", GetRecordFields, GetRecordFields), #"Expanded lists" = Table.TransformColumns(#"Expanded Column1", List.Transform(GetRecordFields, each { _ , each if Value.Is(_, type list) = true then Text.Combine( _ , ",") else _})), #"Replaced errors" = Table.ReplaceErrorValues(#"Expanded lists", List.Transform(GetRecordFields, each {_, 0})), #"Changed Type" = Table.TransformColumnTypes(#"Replaced errors", List.Transform(GetRecordFields, each {_, type text})) in #"Changed Type"
