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.

vojtechsima's avatar
vojtechsima
Super User
9 months ago

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.

 

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