Forum Discussion

Jeze1's avatar
Jeze1
Frequent Visitor
9 months ago
Solved

New to Power Query

Apologies in advance. I am relatively new to Power Query (and coding) and have spent a couple of days looking at forums/guides/etc, and playing with Power Query. I have a JSON file that I have edite...
Need Help
Tutorial Requests
  • vojtechsima's avatar
    vojtechsima
    9 months ago

    Jeze1 ,

    I mean I built it for your original structure where I expected two items, first is headers, the rest is all rows.

    If your JSON will have first row headers and then N number of rows of items, you can do this:

    let
    Source = Json.Document(File.Contents("path.json")),
    
    headers = List.Transform( Source[Rows]{0}[Cells]?, each [Value] ),
    listWithRows = List.Range( Source[Rows], 1),
    getRows = List.Combine(  List.Transform(listWithRows, (l1)=> List.Transform(l1[Rows], (l2)=> List.Transform(l2[Cells], (l3)=> l3[Value]))) ),
    createTable = #table(headers, getRows)
in
    createTable


    Since we have one more list, the process is kind of the same, just one level deeper.

Jeze1's avatar
Jeze1
Frequent Visitor
9 months ago

Hi vojtechsima .

 

Thank you for your help. That worked a treat.

 

I think I understand how it works (sort of) [I'm not that familiar with M language] and trying to apply it to the larger JSON file I have.


An extract of this is below:

 

 

{
    "Rows": [
        {
            "RowType": "Header",
            "Cells": [
            {
              "Value": "Column1"
            },
            {
              "Value": "Column2"
            },
            {
              "Value": "Column3"
            },
            {
              "Value": "Column4"
            },
            {
              "Value": "Column5"
            }
          ]
        },
        {
          "RowType": "Section",
          "Title": "Title1",
          "Rows": [
            {
              "RowType": "Row",
              "Cells": [
                {
                  "Value": "Row1",
                  "Attributes": [
                    {
                      "Value": "XXXXXXXXXX",
                      "Id": "ID1"
                    }
                  ]
                },
                {
                  "Value": "",
                  "Attributes": [
                    {
                      "Value": "XXXXXXXXXX",
                      "Id": "ID1"
                    }
                  ]
                },
                {
                  "Value": "0.00",
                  "Attributes": [
                    {
                      "Value": "XXXXXXXXXX",
                      "Id": "ID1"
                    }
                  ]
                },
                {
                  "Value": "",
                  "Attributes": [
                    {
                      "Value": "XXXXXXXXXX",
                      "Id": "ID1"
                    }
                  ]
                },
                {
                  "Value": "3500.00",
                  "Attributes": [
                    {
                      "Value": "XXXXXXXXXX",
                      "Id": "ID1"
                    }
                  ]
                }
              ]
            },
            {
              "RowType": "Row",
              "Cells": [
                {
                  "Value": "Row2",
                  "Attributes": [
                    {
                      "Value": "YYYYYYYYYYYY",
                      "Id": "ID2"
                    }
                  ]
                },
                {
                  "Value": "",
                  "Attributes": [
                    {
                      "Value": "YYYYYYYYYYYY",
                      "Id": "ID2"
                    }
                  ]
                },
                {
                  "Value": "416.67",
                  "Attributes": [
                    {
                      "Value": "YYYYYYYYYYYY",
                      "Id": "ID2"
                    }
                  ]
                },
                {
                  "Value": "",
                  "Attributes": [
                    {
                      "Value": "YYYYYYYYYYYY",
                      "Id": "ID2"
                    }
                  ]
                },
                {
                  "Value": "2916.69",
                  "Attributes": [
                    {
                      "Value": "YYYYYYYYYYYY",
                      "Id": "ID2"
                    }
                  ]
                }
              ]
            }
          ]
        },
        {
          "RowType": "Section",
          "Title": "Title2",
          "Rows": [
            {
              "RowType": "Row",
              "Cells": [
                {
                  "Value": "Row3",
                  "Attributes": [
                    {
                      "Value": "ZZZZZZZZZ",
                      "Id": "ID3"
                    }
                  ]
                },
                {
                  "Value": "0.00",
                  "Attributes": [
                    {
                      "Value": "ZZZZZZZZZ",
                      "Id": "ID3"
                    }
                  ]
                },
                {
                  "Value": "",
                  "Attributes": [
                    {
                      "Value": "ZZZZZZZZZ",
                      "Id": "ID3"
                    }
                  ]
                },
                {
                  "Value": "3600.00",
                  "Attributes": [
                    {
                      "Value": "ZZZZZZZZZ",
                      "Id": "ID3"
                    }
                  ]
                },
                {
                  "Value": "",
                  "Attributes": [
                    {
                      "Value": "ZZZZZZZZZ",
                      "Id": "ID3"
                    }
                  ]
                }
              ]
            },
            {
              "RowType": "Row",
              "Cells": [
                {
                  "Value": "Row4",
                  "Attributes": [
                    {
                      "Value": "XYZXYZ",
                      "Id": "ID5"
                    }
                  ]
                },
                {
                  "Value": "0.00",
                  "Attributes": [
                    {
                      "Value": "XYZXYZ",
                      "Id": "ID5"
                    }
                  ]
                },
                {
                  "Value": "",
                  "Attributes": [
                    {
                      "Value": "XYZXYZ",
                      "Id": "ID5"
                    }
                  ]
                },
                {
                  "Value": "8000.00",
                  "Attributes": [
                    {
                      "Value": "XYZXYZ",
                      "Id": "ID5"
                    }
                  ]
                },
                {
                  "Value": "",
                  "Attributes": [
                    {
                      "Value": "XYZXYZ",
                      "Id": "ID5"
                    }
                  ]
                }
              ]
            }
          ]
        }
    ]
}

 

I updated your code (see below)

let
Source = Json.Document(File.Contents("C:\Users\XXXXXXXX\Desktop\Test3.json")),
headers = List.Transform( Source[Rows]{0}[Cells]?, each [Value] ),
rows = List.Transform( Source[Rows]{1}[Rows], (lvl1)=> List.Transform(lvl1[Cells], (lvl2)=> lvl2[Value]) ),
rows2 = List.Transform( Source[Rows]{2}[Rows], (lvl1)=> List.Transform(lvl1[Cells], (lvl2)=> lvl2[Value]) ),
rows3 = List.Combine({rows, rows2}),
createTable = #table(headers, rows3)
in
createTable

 

I think I've understood how your code works, and thus could apply it again, but feels like it's not scaleable.

 

rows and rows2 take from lists within the JSON file, but if there is a 3rd list within Rows, I'd have to manually go into the Power Query and create

 

rows3 = List.Transform( Source[Rows]{3}[Rows], (lvl1)=> List.Transform(lvl1[Cells], (lvl2)=> lvl2[Value]) ),
rows4 = List.Combine({rows, rows2, rows3}),

createTable = #table(headers, rows4)

 

 

Does that make sense?

 

Again, thank you for your help!

vojtechsima's avatar
vojtechsima
Super User
9 months ago

Jeze1 ,

I mean I built it for your original structure where I expected two items, first is headers, the rest is all rows.

If your JSON will have first row headers and then N number of rows of items, you can do this:

let
    Source = Json.Document(File.Contents("path.json")),
    
    headers = List.Transform( Source[Rows]{0}[Cells]?, each [Value] ),
    listWithRows = List.Range( Source[Rows], 1),
    getRows = List.Combine(  List.Transform(listWithRows, (l1)=> List.Transform(l1[Rows], (l2)=> List.Transform(l2[Cells], (l3)=> l3[Value]))) ),
    createTable = #table(headers, getRows)
in
    createTable


Since we have one more list, the process is kind of the same, just one level deeper.

  • Jeze1's avatar
    Jeze1
    Frequent Visitor
    9 months ago

    Thank you so much.

     

    It also gives me a deeper understanding of it too, as I try and work out why you got to that answer.

    • vojtechsima's avatar
      vojtechsima
      Super User
      9 months ago

      Jeze1  nice, glad to hear that. If you need a more detailed explanation, let me know. Thanks for the kudos.