Forum Discussion

tfmeier_'s avatar
tfmeier_
New Member
2 years ago
Solved

Table from JSON response for Power BI report

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 A...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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_'s avatar
tfmeier_
New Member
2 years ago

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's avatar
dufoq3
Community Champion
2 years ago

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_'s avatar
    tfmeier_
    New Member
    2 years ago

    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's avatar
      dufoq3
      Community Champion
      2 years ago

      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_'s avatar
        tfmeier_
        New Member
        2 years ago

        Yep that worked 🙂 Thanks for your help