Forum Discussion
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
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
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
Best Regards,
Lin
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)
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.
