Forum Discussion
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...
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
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