Expand List and Column Not Working

[mrknopick]

Hi!

I'm running into an issue where I have combined several json files into a new table, and some of the columns are showing results that just say "list" and when I click on one of them, it has multiple Records underneath (not all the same amount). I'm trying to find a way to get all of that Record Data included in the Rows on this table, and I keep running into walls.  Not sure what I'm doing wrong.  If anyone can provide any guidance/tutorials/assistance, I would really apprecaite it.

Thanks!

John Knopick

[mrknopick]

Starts Out Like This:

When I click on List it gives me this:

And then highlighting one of the records gives me this:

So THIS is the data I'm wanting to have in the initial report/table, I just don't know how to get it there, and all documentation I've looked at so far isn't really helping.  So again ANY Guidance/Tips/Tricks/Etc would be insanely helpful.

[Anonymous]

Hi @mrknopick 

You can refer to the following code to Advanced Editor in power query

let
    Source = {{[a=1,b=4,c=3],[a=1,b=4,c=3],[a=1,b=4,c=3,d=5]},{[],[],[]},{[],[],[]}},
    Custom1 = Table.FromColumns(Source),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"Column2", "Column3"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Record.ToTable([Column1])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Name", "Value"}, {"Name", "Value"})
in
    #"Expanded Custom"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.