Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Power BI Match Column Headers and Fill Rows with single Value from a Different Table?

Table A has thousands of rows of data.    Table B has data structured as such:     A             B 2/28/2019  28 3/31/2019  59 4/30/2019  89 5/31/2019 120 ...etc for 5 years   Essentially w...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

alena2k 

 

Thanks for this suggestion. I'm assuming for this to work I would need to Merge Queries and bring in Column B from Table B to Table A first? No way to do this in one line?

alena2k's avatar
alena2k
Resolver IV
7 years ago

Anonymous 

 

It is not really a merge. You have to prepare B and then "add" it to A, give this a try, you'll see:

 

B

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI3stA3MjC0VNJRMrJQitWJVjLWNzaECZlagoVM9I0NYEIWECFTJFWGRgZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type date}, {"B", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"A", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"A", type text}}, "en-US")[A]), "A", "B", List.Sum)
in
    #"Pivoted Column"

 

 

A

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30jcyMLRQ0lEyBGIDIDbRMzNUitUByRkjy4FwXmlODlTOBF0OqM8IKmeKR58ZTM4Aoc8YKmeORc4EKmeBx0xLLG4xhcoZGmDXGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Working = _t, Holidays = _t, BNR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Working", Int64.Type}, {"Holidays", Int64.Type}, {"BNR", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "C", each B),
    #"Expanded C" = Table.ExpandTableColumn(#"Added Custom", "C", List.Accumulate(#"Added Custom"[C], {}, (state, current) => List.Union({state, Table.ColumnNames(current)})))
in
    #"Expanded C"

IMHO it keeps queries simple and readable. 

  • alena2k's avatar
    alena2k
    Resolver IV
    7 years ago

    However if you have to have a single query, you can follow this example:

     

    let
    Source_B = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI3stA3MjC0VNJRMrJQitWJVjLWNzaECZlagoVM9I0NYEIWECFTJFWGRgZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type B" = Table.TransformColumnTypes(Source_B,{{"A", type date}, {"B", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type B", {{"A", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type B", {{"A", type text}}, "en-US")[A]), "A", "B", List.Sum),

    Source_A = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIAYhM9M0OlWB2IAAjnlebkoAgAVRhhV2GAUGGMLmBC0FBTLCpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A1 = _t, A2 = _t, A3 = _t]),
    #"Added Custom" = Table.AddColumn(Source_A, "C", each #"Pivoted Column"),
    #"Expanded C" = Table.ExpandTableColumn(#"Added Custom", "C", List.Accumulate(#"Added Custom"[C], {}, (state, current) => List.Union({state, Table.ColumnNames(current)})))
in
    #"Expanded C"

    I hope that at least one of them will help :)