Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |