Dynamically expanding all columns with lists

[Bhella]

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

[Bhella · Accepted solution]

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"

[jennratten]

Hello - this is an option you can try.

https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-po...