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
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 =>