Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FoxTerrier
New 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,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.

1 ACCEPTED SOLUTION
Anonymous
Not 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"

View solution in original post

1 REPLY 1
Anonymous
Not 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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors