Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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 🙂
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
Any ideas?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |