Forum Discussion

FoxTerrier's avatar
FoxTerrier
New Member
6 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    6 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"

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 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"