Duplicating a New Column in Power Query

[Estevan]

Hello, 

I am working on data set that is imported via Salesforce Objects into Power Query. On one of my queries I'll have a table that has a column for Department Owner. Then I'll create another table for a different report within the same Power Query but this table will not have a column for Deptartment Owner.

Is it possible to create a new column for Department Owner on this new table, auto filling in or matching the User to their correct department? 

I would be more than happy to provide additonal information or demonstrate. 

Thank you!! 

[v-yanjiang-msft · Accepted solution]

Hi @Estevan ,

According to your description, here's my solution. My sample data is as below.

Table :

User table:

Solution1:

Select the User column in User table, then click "Merge Queries"

Select User column in another table.

In the result column, expand Department column.

Solution2:

Add a custom column:

Table.SelectRows(#"Table", (x)=> x[User]=[User])[Department]{0}

Both get correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RocketRod]

Yes, can merge the tables to add the column into the new table, or write M code to do it if lookup logic is complex.

[Estevan]

Hi RocketRod, 

Are you available to provide further support? This would be a huge help for me so I can move forward with this roadblock.