The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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
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:
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