Naming expanded rows in Multidimensional JSON

[RuneStrand]

Hi All 

So, I am working with multidimensional JSON, with nested JSON data. 

I have figured out how to do a generic naming of the data in the nested JSON, by first adding a custom column creating a list of headers using Record.FieldNames, then doing "drilldown" - removing duplicates, then expanding the record and using the list as headers. This way I can freely add new columns to the JSON, and it will come up in my Query. 

(I used the procedure explained here: https://www.youtube.com/watch?v=tzLdep_Ai5U to do this)

Now my issue comes:

Let's say that the nested JSON has the name "Test", and the data inside it when expanded has columns called "1", "2", "3", and so forth. When I use the procedure above, the "parent" name "Test" dissapears, and columns are now just 1,2,3, etc.. 

I have multiple nested JSON's in my JSON, and I will end up with several columns just called 1,2,3,etc.. whitout knowing where they come from. 

So is there a way, either an addition to the procedure in the video, or another way - to simply add the parent name of the JSON to the column names - while doing it generically, so that I can freely add new data to my JSON, and it will come up as a new column in my PowerBi data model?

(

I was thinking something like:

"    Table.ExpandRecordColumn(Custom1, "Column1", "TEST"+header    "

Note: my list of headers is called "header" - "Test" is the part I would like to add to the name

it could also happen earlier, when I create the list, I just haven't been able to find the the function that allows me to add text to every row on a list

)

I hope this makes sens?

[v-deddai1-msft]

Hi @RuneStrand ,

I don't quite understand your description. Would you please show us screenshot and M-query for it?

Best Regards,

Dedmon Dai

[RuneStrand]

Hi @v-deddai1-msft 

My PowerBi is running in Danish language - so I've tried to translate the danish names manually - so if there are small type-o's that's why)

The code from the advanced editor is:

    #"Custom1" = Table.AddColumn(#"Expanded Column1", "QC points header", each Record.FieldNames([qc_points])),

    #"QC points header" = List.Combine(#"Custom1"[QC points header]),

    #"QC Headers" = List.Distinct(#"QC points header"),

    Custom2 = #"Expanded Column1",

    #"Expanded qc_points" = Table.ExpandRecordColumn(Custom2, "qc_points", #"QC Headers")

The issue is that the list I'm creating "QC Headers", contains just numbers (1,2,3,etc..) - and we need it this was in the database. But for my PowerBi Model I want QC1,QC2,QC3,etc... or qc_points1, qc_points2, qc_points3,etc.. basically just a way to add text to each field in the list, so that when I use this list as headers in "Table.ExpandRecordColumn(Custom2, "qc_points", #"QC Headers")", I will know where they came from.

I have multiple records that I need to do this operation on, and just having 1,2,3 as headers on all of them, once expanded, doesn't work then.

I hope it makes more sense now.