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
Anonymous
Not applicable

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 what I want to do is tack on column A from table B as Column Headers in Table A. And then for every existing row of data already there in Table A I want the new columns from Table B (2/28/2019, 3/31/2019...) to have values from Column B. So for every row in Table A, the new column of 2/28/2019 would have a value of 28.

 

8 REPLIES 8
alena2k
Resolver IV
Resolver IV

After you pivoted table B, you can add it as a Custom Column to the table A and then expand all in in this column

= Table.AddColumn(A, "Custom", each B)

 

try.png

Anonymous
Not applicable

@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?

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

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 🙂

v-lili6-msft
Community Support
Community Support

hi, @Anonymous 

Do you mean that column headers of table A is like this 2/28/2019, 3/31/2019...

If so, I suggest you to Unpivot table A or Pivot tabke B then create the relationship or merge two table.

http://radacad.com/pivot-and-unpivot-with-power-bi

 

If not your case, please share some sample data from table A and expected output. 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-lili6-msftThanks for the reply. I sort have already tried your suggestion. I tried to unpivot and then merge the two tables. The issue is that there are no matching rows between Table A and Table B so nothing returns. If I make the join to include all rows from each table that does not really help either as the thousands of rows from Table A won't have any values in the new merged column since there are no matching rows.


I really want to just dump the value of a column in Table B into every row in Table A.

hi, @Anonymous 

Please share some sample data(or virtual data) from table A and expected output. 

and if you could try Transpose function in power query

9.JPG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Any ideas?

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.