Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
Jeze1
Frequent Visitor

New to Power Query

‎10-30-2025 06:22 AM

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 edited (if I can get my head around the shortened version of the file, I can work with the longer version in due course), and then linked to Power Query. I want to extract the data into a table, and have managed to do so, but think it is a long/overly complicated way, and also includes lines where I have had to create records from lists, but these are fixed (i.e. if the list becomes 3 lines long, I won't get the 3rd item).

 

This is my JSON:

{
    "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"
                    }
                  ]
                }
              ]
            }
          ]
        }
    ]
}

 

 

This is my current Power Query:

let

    Source = Json.Document(File.Contents("C:\Users\XXXXXXX\Desktop\test3.json")),

    Rows = Source[Rows],

    Rows1 = Rows{0},

    Cells = Rows1[Cells],

    #"Converted to Table" = Table.FromList(Cells, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Value"}, {"Column1.Value"}),

    RowHeaders = Table.Column(#"Expanded Column1","Column1.Value"),

 

    Rows2 = Source[Rows],

    #"Converted to Table1" = Table.FromList(Rows2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"RowType", "Cells", "Title", "Rows"}, {"Column1.RowType", "Column1.Cells", "Column1.Title", "Column1.Rows"}),

    #"Expanded Column1.Rows" = Table.ExpandListColumn(#"Expanded Column2", "Column1.Rows"),

    #"Expanded Column1.Rows1" = Table.ExpandRecordColumn(#"Expanded Column1.Rows", "Column1.Rows", {"RowType", "Cells"}, {"Column1.Rows.RowType", "Column1.Rows.Cells"}),

    #"Expanded Column1.Rows.Cells" = Table.ExpandListColumn(#"Expanded Column1.Rows1", "Column1.Rows.Cells"),

    #"Expanded Column1.Rows.Cells1" = Table.ExpandRecordColumn(#"Expanded Column1.Rows.Cells", "Column1.Rows.Cells", {"Value"}, {"Column1.Rows.Cells.Value"}),

    Custom2 = Table.Column(#"Expanded Column1.Rows.Cells1", "Column1.Rows.Cells.Value"),

    Custom3 = List.RemoveFirstN(Custom2),

    Custom4 = List.Split(Custom3, 5),

    Custom5 = Record.FromList(Custom4{0}, RowHeaders),

    Custom6 = Record.FromList(Custom4{1}, RowHeaders),

    Custom7 = Table.FromRecords({Custom5, Custom6})

in

    Custom7

 

 

It puts the output into the format I'd like, but you can see Custom5 and Custom6 are 'fixed', as is Custom7.

 

If a Row3 appeared in the JSON file, then my output wouldn't include Row3.

 

 

I don't care about the attributes within the JSON (just unnecessary output when I got the JSON file) so these can be ignored.

 

 

Again, apologies if this is super basic, and please feel free to delete this or tell me to do more research/practice. 

Labels:
Message 1 of 6
352 Views
0
1 ACCEPTED SOLUTION
vojtechsima
Super User
Super User
In response to Jeze1

‎10-30-2025 10:53 AM

@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.

View solution in original post

Message 4 of 6
290 Views
5 REPLIES 5
vojtechsima
Super User
Super User

‎10-30-2025 07:52 AM

Hey @Jeze1 

,

definitely not easy for new comers, if you don't specialize in M, you probably won't do it, so totally fair to strugle, try this, should be stable for your schema, as long as you follow the structure of row1 = headers, row2 = rows.

 

vojtechsima_0-1761835918650.png

let
    Source = Json.Document(File.Contents("path.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]) ),

    createTable = #table(headers, rows)
in
    createTable
Message 2 of 6
322 Views
0
Jeze1
Frequent Visitor
In response to vojtechsima

‎10-30-2025 08:53 AM

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!

Message 3 of 6
301 Views
0
vojtechsima
Super User
Super User
In response to Jeze1

‎10-30-2025 10:53 AM

@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.

Message 4 of 6
291 Views
Jeze1
Frequent Visitor
In response to vojtechsima

‎10-30-2025 10:59 AM

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.

Message 5 of 6
274 Views
0
vojtechsima
Super User
Super User
In response to Jeze1

‎10-30-2025 11:49 AM

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

Message 6 of 6
267 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Kudoed Authors
View All