Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Power BI Community,
I need your help. This is my JSON source file:
{
"labels": ["2019-08","2019-09","2019-10"],
"indicators":[
{"name":"Indicator One", "values": [29149,28982,25681]}
,{"name":"Indicator Two", "values": [1945,1672,1299]}
,{"name":"Indicator Three", "values": [5131,5122,5549]}
]
}
For each indicator from the indicators list I need to link values ("values") with labels from a different, global list ("labels") .
Question is: how to design Power Query to get a result table like this:
Indicator One | 2019-08 | 29149
Indicator One | 2019-09 | 28982
Indicator One | 2019-10 | 25681
Indicator Two | 2019-08 | 1945
Indicator Two | 2019-09 | 1672
Indicator Two | 2019-10 | 1299
Indicator Three | 2019-08 | 5131
Indicator Three | 2019-09 | 5122
Indicator Three | 2019-10 | 5549
Any help much appreciated. Thank you.
Solved! Go to Solution.
Ok, try this query
let
Source = Json.Document(File.Contents("C:\t\my.json")),
SourceLabels = List.Buffer( Source[labels]),
SourceIndicators = Table.FromRecords(Source[indicators]),
AddLabels = Table.AddColumn(SourceIndicators, "Labels", each List.Transform(List.Positions(SourceLabels),
(el) => [Label=SourceLabels{el}, Value=[values]{el}])),
#"Removed Columns" = Table.RemoveColumns(AddLabels,{"values"}),
#"Expanded Labels" = Table.ExpandListColumn(#"Removed Columns", "Labels"),
#"Expanded Labels1" = Table.ExpandRecordColumn(#"Expanded Labels", "Labels", {"Label", "Value"}, {"Label", "Value"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Labels1", {{"Value", type text}}, "en-US"),{"name", "Label", "Value"},Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"Merged")
in
#"Merged Columns"
Ok, try this query
let
Source = Json.Document(File.Contents("C:\t\my.json")),
SourceLabels = List.Buffer( Source[labels]),
SourceIndicators = Table.FromRecords(Source[indicators]),
AddLabels = Table.AddColumn(SourceIndicators, "Labels", each List.Transform(List.Positions(SourceLabels),
(el) => [Label=SourceLabels{el}, Value=[values]{el}])),
#"Removed Columns" = Table.RemoveColumns(AddLabels,{"values"}),
#"Expanded Labels" = Table.ExpandListColumn(#"Removed Columns", "Labels"),
#"Expanded Labels1" = Table.ExpandRecordColumn(#"Expanded Labels", "Labels", {"Label", "Value"}, {"Label", "Value"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Labels1", {{"Value", type text}}, "en-US"),{"name", "Label", "Value"},Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"Merged")
in
#"Merged Columns"