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 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's avatar
    ronrsnfld
    Super User
    2 years ago

    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.