JSON with columns and rows separate that cant be merged into 1 combined row

[ariten]

Hi All, 

Hoping you may be able to help me here. 

I have data that i am pulling via a web request, however due to the structure of the data i am having a hard time extracting out the rows and columns, im stuck and have tried multiple different ways. 

The data structure is pictured below. 

This data is psuedo but is representive of the strucuture i am dealing with (10  Columns instead of 4). I am able to extract the column headers out and transpose them into headers, however i am having a issue with the rows, I cant get them to into the list of 4 sperorate columns to reprsent the row to then extract the value for each record. When i try and expand the list i all 4 records merged into 1 column. Pictured below. 

after extraction

I think im missing a step where i should be able to expand the list across multiple columns or do a sort of condition based transpose, i.e row1 gets id 1 etc.

Any thoughts?

[v-yueyunzh-msft]

Hi, @ariten 

Do you mean you want to filter the list in the [Column1.colData]?

If this , you can use List.Select() function to filter the data you want and then you expand your list .

List.Select - PowerQuery M | Microsoft Learn

If this does not help you solve the problem, you can also provide us with some test data and the results you want to generate in the end, so that we can help you better.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ariten]

Hi @v-yueyunzh-msft, Thanks for replying. 

This is a anonomysed data pull from the source. List.Select is not what im looking for, i need to be able to expand these rows across multiple columns and PowerBi is just recogniseing them as 1 column

[v-yueyunzh-msft · Accepted solution]

Hi, @ariten 

Oh..i know you want to convert this list to a column like 4 columns in a table.

This is my test .json file as a sample data:

You can put this in "Advanced Editor" to refer to:

let
    Source = Json.Document(File.Contents("E:\wicresoft_dict\New Text Document.json")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded Columns" = Table.ExpandRecordColumn(#"Converted to Table", "Columns", {"Column"}, {"Columns.Column"}),
    #"Expanded Rows" = Table.ExpandRecordColumn(#"Expanded Columns", "Rows", {"Row"}, {"Rows.Row"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Rows",{{"Columns.Column", "Column"}, {"Rows.Row", "Row"}}),
    RowData = List.Transform(#"Renamed Columns"[Row]{0},(x)=>  List.Transform(x,(y)=>y[ColData]) ),
    ColumnName = List.Transform(#"Renamed Columns"[Column]{0},(x)=>x[ColTitle]),
    Custom1 = Table.FromRows(RowData,ColumnName)
in
    Custom1

Then we can get the table you want to :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Steve_ABC]

Hi there. I have the same problem to solve, but my JSON structure is different. I've tried to amend it to my use case but can't get it to work. Please help. An generic example of my data structure:

{
    "DataSource": {
        "Name": "Example Data",
        "Headers": [
            {
                "Name": "Column 1",
                "Type": "Text"
            },
            {
                "Name": "Column 2",
                "Type": "Text"
            },
            {
                "Name": "Column 3",
                "Type": "Text"
            },
            {
                "Name": "Column 4",
                "Type": "Text"
            }
        ],
        "Rows": [
            [
                "123-Test",
                "Test",
                "Test",
                "Test"
            ],
            [
                "234-Test",
                "Test",
                "Test",
                "Test"
            ],
            [
                "345-Test",
                "Test",
                "Test",
                "Test"
            ],
            [
                "456-Test",
                "Test",
                "Test",
                "Test"
            ]
        ],
        "TotalRows": 4
    }
}

I need this to output: 

Column 1	Column 2	Column 3	Column 4
123-Test	Test	Test	Test
234-Test	Test	Test	Test
345-Test	Test	Test	Test
456-Test	Test	Test	Test

[ariten]

Hi @Steve_ABC & @Damien25 

Please try 

let
    Source = Json.Document(File.Contents("C:\Users\Public\test.json")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"DataSource" = #"Converted to Table"{0}[DataSource],
    #"Headers" = List.Transform(#"DataSource"[Headers],(x)=>x[Name]),
    Custom1 = Table.FromRows(#"DataSource"[Rows],#"Headers")
in
    Custom1

Replace the Source section with your data source. 

This is outputting the tbale in the format you were looking for. 

[Damien25]

Hi @Steve_ABC 

I'm facing same json schema structure and I'm not able to figure it out. Would you have an example to share that match your schema above?

Thanks,
D.

[ariten]

Thanks this got me on the right track, had to make 1 very small change to account for difference in our structures but it all works now, Thanks!

[ariten]

Point of clarification in my data structure, Row is a list of Records, these records contain the field ColData which contains a list of records which have a field called "value" that the value of that cell.