Using different table in conditional column

[Anonymous]

I need to organize a column into separate columns based on a different reference table and I'm having a hard time figuring it out in PQ.

Example:

Original	Column1	Column2
A	A
B	B
C		C
D		D

Column1 and Column2 are part of another table that looks like this:

Colum1	Column2
A	C
B	D

Each time "A" appears in the Original column then the contents should be copied into Column1 and so on. Each cell below the column header is named so I'm able to reference it later. e.g. cell with "A" is named Column1.1

I used a conditional column to do this, but I'm having a hard time referencing the second table in the code. 

= Table.AddColumn(#"Changed Type", "Column1", each if Text.Contains([Original], Column1.1) then "01_"& Column1.1 else null)

Any help is much appreciated!

[mussaenda]

Hi @Anonymous,

did @edhans solve your problem?

if not please show your expected output.

Thanks!

[edhans · Accepted solution]

I don't understand. For example:

"Each time "A" appears in the Original column then the contents should be copied into Column1 and so on."

What do you mean "and so on?" You gave one example. I cannot extrapolate from that, other than to put everything in column 1.

Cells don't exist in Power Query or Power BI like they do in Excel, so you cannot name a cell Column1.1, or anything else, so I'm not clear on that either. Columns can be named - must be named. Rows cannot be named, or individual datapoints in a row or column.

But let me take a few guesses. You have this:

And you want to return this:

First thing I did was unpivoted your 2nd table

so it becomes this - I left the default column names. You just select both columns then select Unpivot Columns.

Then I merged taht into table 1 connecting the Original and Values columns, then expanded both columns:

Then I pivoted the Attribute column with the following settings - Click the Attribute column, then in the Transform ribbon, the Pivot Column button - you must stop the aggregation as I have done below.

You can see my full PBIX file here. The null values will look blank when you load them into Power BI.