Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |