Forum Discussion
FoxTerrier
6 years agoNew Member
Link values with labels from another list
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,2568...
- Anonymous6 years ago
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"
Anonymous
6 years agoNot applicable
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"