Table from JSON response for Power BI report

[tfmeier_]

I need to create a simple barchart showing 'Sales Amount' by week. The x-axis represents weeks and the y-axis aggregates 'Sales Amount' for a given week.

The data for this comes from Papyrs via an API call and produces the following JSON response. This example shows 2 job cards (ID 29 and 28) with the corresponding data (Start Date and Sales Amount)

[
    [
        {
            "field": "ID",
            "value": 29
        },
        {
            "field": "Start Date",
            "value": "06/08/2024 15:18"
        },
        {
            "field": "Created by",
            "value": "xxxx"
        },
        {
            "field": "Job Card Status",
            "value": "Final"
        },
        {
            "field": "Sales Amount",
            "value": "2500"
        }
    ],
    [
        {
            "field": "ID",
            "value": 28
        },
        {
            "field": "Start Date",
            "value": "06/08/2024 15:16"
        },
        {
            "field": "Created by",
            "value": "yyyy"
        },
        {
            "field": "Job Card Status",
            "value": "Final"
        },
        {
            "field": "Sales Amount",
            "value": "15400"
        }
    ]
]

I can't find a way in Power Query to process this data as the API call (use the connector web) creates this 2 column table

and with my limited Power Query skills I can't transform this table to the target table with the columns ID, Start Date and Sales Amount which I then can use to create the bar chart.

Any help would be much appreciated

[dufoq3]

Hi @tfmeier_, different approach here. If you don't know how to use my query - read note below my post.

Output

v1

let
    Source = "[#(cr)#(lf)    [#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""ID"",#(cr)#(lf)            ""value"": 29#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Start Date"",#(cr)#(lf)            ""value"": ""06/08/2024 15:18""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Created by"",#(cr)#(lf)            ""value"": ""xxxx""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Job Card Status"",#(cr)#(lf)            ""value"": ""Final""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Sales Amount"",#(cr)#(lf)            ""value"": ""2500""#(cr)#(lf)        }#(cr)#(lf)    ],#(cr)#(lf)    [#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""ID"",#(cr)#(lf)            ""value"": 28#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Start Date"",#(cr)#(lf)            ""value"": ""06/08/2024 15:16""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Created by"",#(cr)#(lf)            ""value"": ""yyyy""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Job Card Status"",#(cr)#(lf)            ""value"": ""Final""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Sales Amount"",#(cr)#(lf)            ""value"": ""15400""#(cr)#(lf)        }#(cr)#(lf)    ]#(cr)#(lf)]",
    JsonToTable = Table.Combine(List.Transform(Json.Document(Source), Table.FromRecords)),
    ToList = List.TransformMany(Table.ToColumns(JsonToTable),
        each List.Split(_, 5),
        (x,y)=> y ),
    ToTable = Table.FromRows(ToList),
    PromotedHeaders = Table.PromoteHeaders(ToTable, [PromoteAllScalars=true]),
    FilteredRows = Table.SelectRows(PromotedHeaders, each ([ID] <> "ID"))
in
    FilteredRows

v2

let
    Source = "[#(cr)#(lf)    [#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""ID"",#(cr)#(lf)            ""value"": 29#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Start Date"",#(cr)#(lf)            ""value"": ""06/08/2024 15:18""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Created by"",#(cr)#(lf)            ""value"": ""xxxx""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Job Card Status"",#(cr)#(lf)            ""value"": ""Final""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Sales Amount"",#(cr)#(lf)            ""value"": ""2500""#(cr)#(lf)        }#(cr)#(lf)    ],#(cr)#(lf)    [#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""ID"",#(cr)#(lf)            ""value"": 28#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Start Date"",#(cr)#(lf)            ""value"": ""06/08/2024 15:16""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Created by"",#(cr)#(lf)            ""value"": ""yyyy""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Job Card Status"",#(cr)#(lf)            ""value"": ""Final""#(cr)#(lf)        },#(cr)#(lf)        {#(cr)#(lf)            ""field"": ""Sales Amount"",#(cr)#(lf)            ""value"": ""15400""#(cr)#(lf)        }#(cr)#(lf)    ]#(cr)#(lf)]",
    JsonToTable = Table.Combine(List.Transform(Json.Document(Source), Table.FromRecords)),
    Transformed = Table.Combine(List.Transform(Table.Split(JsonToTable, 5), each Table.PromoteHeaders(Table.FromRows(Table.ToColumns(_)))))
in
    Transformed

[tfmeier_]

Thanks. I thought my sample data was provided correctly. Have successfully tried to other option and I'm not trying to get the data directly from the API per my other response

[dufoq3]

If you have this table, ignore Source and JsonToTable steps from my queries. You have to replace number 5 in my queries with number of columns for each ID - if you don't know hot to do it - let me know.

[tfmeier_]

Yes, I don't think I follow you. The following code is what I use to get the API data and what creates the field / value table

let
    Source = Json.Document(Web.Contents("API_ENDPOINT_URL")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column11" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"field", "value"}, {"field", "value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column11",{{"field", type text}, {"value", type any}})
in
    #"Changed Type"

but I fail integrating your code (without Source and JsonToTable steps) here without errors

[dufoq3 · Accepted solution]

Try this and let me know:

let
    Source = Json.Document(Web.Contents("API_ENDPOINT_URL")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column11" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"field", "value"}, {"field", "value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column11",{{"field", type text}, {"value", type any}}),
    SplitBy = Table.RowCount(#"Changed Type") / List.Count(List.Select(#"Changed Type"[field], (x)=> x = "ID")),
    Transformed = Table.Combine(List.Transform(Table.Split(#"Changed Type", SplitBy), each Table.PromoteHeaders(Table.FromRows(Table.ToColumns(_)))))
in
    Transformed

[tfmeier_]

Yep that worked 🙂 Thanks for your help

[dufoq3]

You're welcome 😉

[tfmeier_]

Thanks for the quick reply. Exactly what's needed to get this data presented.

Given the data comes from an API call as opposed to the local sample data used in the example, how would I change the first step - assuming I can directly process the API response without creating a local json file and referencing that?

[ronrsnfld]

You should have a series of steps that results in the two column table you show in your question.  And if you look at the Applied Steps in my query, you will see that I have a different list of steps that results in the two column table. In the Advanced Editor, you replace the steps that I used with the steps that you used. In the first step of mine that you are using, you may also need to change the table reference, and possibly some column references, depending on how limited your supplied JSON is.

[ronrsnfld]

You need to Pivot the data to put it into rows.

Because of the way Power Query handles pivoting with no aggregation, you'll need to group it by the different ID's. This can be done by using the optional 4th and 5th arguments of the Table.Group function.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wio7JUwACKAUC1QgmCMQopWWm5qTEKFkBmZ4uMUo66PJliTmlqSB5I0uEVK0OcSYGlyQWlSi4JJak4jM5RsnATN/AQt/IwMhEwdDUytAiRol0u5yLUoH2pCgkVeK3qwIIyDHfKz9JwTmxKEUB6KmS0mL8lrhl5iXmkGNLcGJOarGCY25+aV4JfiuMTA0MUGyAMGN1qBnnFnSLczNaxnklEAyHODc0NcEe6TF5sUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    json = Json.Document(#"Changed Type"[Column1]{0}),
    #"Converted to Table" = Table.FromList(json, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"field", "value"}, {"field", "value"}),
    #"Grouped Rows" = Table.Group(#"Expanded Column2", {"field"}, {
        {"Pivot", each Table.Pivot(_,[field],"field","value")}},
        GroupKind.Local,(x,y)=>Number.From(y[field]="ID")),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"field"}),
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Removed Columns", "Pivot", {"ID", "Start Date", "Created by", "Job Card Status", "Sales Amount"}, {"ID", "Start Date", "Created by", "Job Card Status", "Sales Amount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Pivot",{
        {"ID", Int64.Type}, {"Start Date", type datetime}, {"Created by", type text}, {"Job Card Status", type text}, {"Sales Amount", Int64.Type}})
in
    #"Changed Type1"

From your data =>