Create a dim table with distinct of column from fact table in the power query

[emohammad]

Hi,

I have multiple fact tables and would like to create dim tables from each fact table in the power query to make further joins and an effective relationship between multiple fact tables.

To create a Dim table, I want a distinct column from the fact table.

I performed the below steps to create a dim table but not able to get accurate results.

Home --> Transform Data --> select fact table --> select ‘duplicate’ (to make this as dim table) --> choose column --> select the column --> right click on the column --> Remove Duplicates

But in the resultant data, I could see few duplicate values, and also, few values are missing in the dim table.

Please help me with this.

[v-jingzhang]

Hi @emohammad 

Could you provide some sample data or pbix file (remove sensitive data) which can reproduce this issue, so that we can help check the details.

Another option is to use DAX functions SUMMARIZE or DISTINCT to create Dim tables if you are using Power BI Desktop. For example, 

DimTable = SUMMARIZE ( 'table', 'table'[columnName] )

DimTable = DISTINCT ( 'table'[columnName] )

Regards,
Community Support Team _ Jing

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

[emohammad]

Hi @Fowmy ,
Thanks for your reply.

When I selected remove other columns and followed by clicking on remove empty in the filter dropdown, it got the missing values. But now, my only problem is with the duplicates in the dim table. 

I have a confusion here that, I could see the single record in both the fact and dim table when I checked manually in the power query tables. But I could see the multiple records for that particular values in the report and data panel. Not sure what went wrong.

Note: This issue is not with all the dataset but only for few values/records

[Fowmy]

@emohammad 

If you can share a screenshot or share the PBIX file without the confidential data, I will be able to see what is exactly is wrong with the report.

[Fowmy · Accepted solution]

@emohammad 

Before Remove Duplicate action, choose Remove Other Columns. This way, you will get only unique values.