Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

PowerQuery: Distinct table based on another table

Hi there,

 

Would like to get some help here please...

 

I know the most preferred way to deal with this is PowerQuery, unfortunately using Reference won't work here... here's why

 

The original table has Employee Name and Employee ID:

Employee NameEmployee ID
Employee AAA1
Employee AAA1
Employee AAA1
Employee BBB2
Employee BBB2
Employee BBB2
Employee CCC3
Employee CCC3
Employee CCC3
Employee DDD4
Employee DDD4
Employee DDD4

 

So where I'm going at is I want to get rid of the Employee Name column and build a dimension table based on the table above.

 

I think this is enough information, so how do you usually deal with this?

 

I'm not sure if "Duplicate" is ideal since the original table is dynamic... if it changes would the duplicated table adapt changes made to the original table?

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
Microsoft Employee


@ovetteabejuela wrote:

Hi there,

 

Would like to get some help here please...

 

I know the most preferred way to deal with this is PowerQuery, unfortunately using Reference won't work here... here's why

 

The original table has Employee Name and Employee ID:

Employee Name Employee ID
Employee AAA 1
Employee AAA 1
Employee AAA 1
Employee BBB 2
Employee BBB 2
Employee BBB 2
Employee CCC 3
Employee CCC 3
Employee CCC 3
Employee DDD 4
Employee DDD 4
Employee DDD 4

 

So where I'm going at is I want to get rid of the Employee Name column and build a dimension table based on the table above.

 

I think this is enough information, so how do you usually deal with this?

 

I'm not sure if "Duplicate" is ideal since the original table is dynamic... if it changes would the duplicated table adapt changes made to the original table?


@ovetteabejuela

Both Duplicate and Reference shall work for you, I think. The only difference is the duplicated changes according to your datasource and the referencing  change according to the table query, if you compare the Power Query code in Advanced editor.

e.g If you remove a column from the table query, the change only reflects to the referencing. If you change something in the datasource, the change reflects to both the duplicated and the referencing.

 

Capture.PNGCapture2.PNG

View solution in original post

2 REPLIES 2
Eric_Zhang
Microsoft Employee
Microsoft Employee


@ovetteabejuela wrote:

Hi there,

 

Would like to get some help here please...

 

I know the most preferred way to deal with this is PowerQuery, unfortunately using Reference won't work here... here's why

 

The original table has Employee Name and Employee ID:

Employee Name Employee ID
Employee AAA 1
Employee AAA 1
Employee AAA 1
Employee BBB 2
Employee BBB 2
Employee BBB 2
Employee CCC 3
Employee CCC 3
Employee CCC 3
Employee DDD 4
Employee DDD 4
Employee DDD 4

 

So where I'm going at is I want to get rid of the Employee Name column and build a dimension table based on the table above.

 

I think this is enough information, so how do you usually deal with this?

 

I'm not sure if "Duplicate" is ideal since the original table is dynamic... if it changes would the duplicated table adapt changes made to the original table?


@ovetteabejuela

Both Duplicate and Reference shall work for you, I think. The only difference is the duplicated changes according to your datasource and the referencing  change according to the table query, if you compare the Power Query code in Advanced editor.

e.g If you remove a column from the table query, the change only reflects to the referencing. If you change something in the datasource, the change reflects to both the duplicated and the referencing.

 

Capture.PNGCapture2.PNG

Hi @Eric_Zhang,

 

 

I will mark this as resolved just because it appears as though there is no elegant solution for my requirement.

 

Referencing is the most elegant between the two however it does not meet my requirement. again my requirement is to build a dimension table out of the original table however I could not delete unecessary column from the original table, for example and to reiterate If I need to create a dimension table for Employee ID and Employee Name, It's possible but I would want to delete the Employee Name from the original table since I already have it in the Refence table (which is not possible).

 

Maybe I'll just stick with duplicating, which means duplicating the time to process the same table as well.

 

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.