Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ranbeermakin
Resolver III
Resolver III

Power Query M Language Question: How to best convert json with rows/cols into a table?

Hi there,

 

Any idea how to best convert the following json into a table:

 

Expected output:

 

assignment_reference | amount

-------------------------------------

19941136 | -0.01

19941145 | -0.01

 

 

Json:

 

{
"table":{
"cols":[
{
"label":"assignment__reference",
"type":"reference"
},
{
"label":"amount",
"type":"number"
}
],
"rows":[
{
"c":[
{
"v":"19941136"
},
{
"v":"-0.01"
}
]
},
{
"c":[
{
"v":"19941145"
},
{
"v":"-0.01"
}
]
}
]
}
}

 

Note:

1. I know the column names in advance so I need not parse "cols" object.

 

Any help will be appreciated.

 

Thanks

Ranbeer

1 ACCEPTED SOLUTION

Thanks everyone, I was able to do this with this M query

 

 

let
    Source = Json.Document(File.Contents("D:\sample.json")),
    Table = Record.ToTable(Source),
    R = Table.ExpandRecordColumn(Table, "Value", {"rows", "cols"}, {"rows", "cols"}),
    #"Expanded rows" = Table.ExpandListColumn(R, "rows"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded rows",{"cols"}),
    t = Table.ExpandRecordColumn(#"Removed Columns", "rows", {"c"}, {"c"}),
    col1 = Table.AddColumn(t, "reference", each if Value.Is([c], type list) then List.First([c]) 
                                                             else [c]),
        col2 = Table.AddColumn(col1, "amount", each if Value.Is([c], type list) then List.Last([c]) else [c]),
        expandedReference = Table.ExpandRecordColumn(col2, "reference", {"v"}, {"contract_id"}),
        expandedAmount = Table.ExpandRecordColumn(expandedReference, "amount", {"v"}, {"amount_spent"})
in
    expandedAmount

 

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

Hi ranbeermakin,

 

Firstly, modify you json file like this:

{
   "table":{
      "cols":[
         {
            "label":"assignment__reference",
            "type":"amount"
         }
      ],
      "rows":[
         {
             "assignment__reference":"19941136",
             "amount":"-0.01"
         },
         {

             "assignment__reference":"19941145",
             "amount":"-0.01"
         }
      ]
   }
}

Then load this file into power bi, click Query Editor->View-> Advanced Editor using M code like this pattern:

let
    Source = Json.Document(File.Contents("C:\Users\Administrator\Desktop\data2.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"cols", "rows"}, {"Value.cols", "Value.rows"}),
    #"Expanded Value.cols" = Table.ExpandListColumn(#"Expanded Value", "Value.cols"),
    #"Expanded Value.rows" = Table.ExpandListColumn(#"Expanded Value.cols", "Value.rows"),
    #"Expanded Value.cols1" = Table.ExpandRecordColumn(#"Expanded Value.rows", "Value.cols", {"label", "type"}, {"Value.cols.label", "Value.cols.type"}),
    #"Expanded Value.rows1" = Table.ExpandRecordColumn(#"Expanded Value.cols1", "Value.rows", {"assignment__reference", "amount"}, {"Value.rows.assignment__reference", "Value.rows.amount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value.rows1",{"Name", "Value.cols.label", "Value.cols.type"})
in
    #"Removed Columns"

The result is like this:

2.PNG 

 

Regards,

Jimmy Tao

Thanks Jimmy. That is very close. Unfortunately, I cannot modify the json, the structure is provided by my service provider.

 

Any other thoughts on how accomplish this? I'm also brainstorming...

 

Ranbeer

Thanks everyone, I was able to do this with this M query

 

 

let
    Source = Json.Document(File.Contents("D:\sample.json")),
    Table = Record.ToTable(Source),
    R = Table.ExpandRecordColumn(Table, "Value", {"rows", "cols"}, {"rows", "cols"}),
    #"Expanded rows" = Table.ExpandListColumn(R, "rows"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded rows",{"cols"}),
    t = Table.ExpandRecordColumn(#"Removed Columns", "rows", {"c"}, {"c"}),
    col1 = Table.AddColumn(t, "reference", each if Value.Is([c], type list) then List.First([c]) 
                                                             else [c]),
        col2 = Table.AddColumn(col1, "amount", each if Value.Is([c], type list) then List.Last([c]) else [c]),
        expandedReference = Table.ExpandRecordColumn(col2, "reference", {"v"}, {"contract_id"}),
        expandedAmount = Table.ExpandRecordColumn(expandedReference, "amount", {"v"}, {"amount_spent"})
in
    expandedAmount

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.