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,2568...
  • 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"