Combine unrelated columns in power query

user_17727322 0
Viewed 165

I am trying to make a new table from columns in two unrelated tables. I need one column from table A to be the headers in the new table and one column to be a column in the new table. I attached a picture of what I am trying to achieve. This is just an example of the problem. The real problem is much larger and had a dynamic number of rows. I was thinking maybe store these two columns as lists and then somehow combine them?...not sure. Appreciate any feedback!

enter image description here

powerbi powerquery excel
1 Answers

There is probably 900 ways to do this. Offhand, try this

let
Columns= #table(Table1[Teams],{}),
Rows=Table.SelectColumns(Table2,"People"),
Combined = Rows & Columns
in Combined

enter image description here

Note, you have not provided any info on how to fill in the cells

Also, in 90% of the cases, you'd probably rather have the below set up where you can then add formulas to populate the cells before pivoting it back

enter image description here

using code:

let combined=Table.AddColumn(Table.SelectColumns(Table2,"People"),"Team", each Table1[Teams]),
#"Expanded Team" = Table.ExpandListColumn(combined, "Team")
in #"Expanded Team"
user_92642300
Related