Forum Discussion

RuneStrand's avatar
RuneStrand
Frequent Visitor
6 years ago

Naming expanded rows in Multidimensional JSON

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?

 

Need Help

2 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    6 years ago

    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's avatar
      RuneStrand
      Frequent Visitor
      6 years ago

      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.