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