Forum Discussion

ranbeermakin's avatar
ranbeermakin
Resolver III
8 years ago
Solved

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...
Need Help
  • ranbeermakin's avatar
    ranbeermakin
    8 years ago

    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

     

ranbeermakin's avatar
ranbeermakin
Resolver III
8 years ago

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

ranbeermakin's avatar
ranbeermakin
Resolver III
8 years ago

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