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
sambgv
Helper I
Helper I

Distinct values increase after just removing columns

Hi! 

 

I spotted a strange behaviour in datasets that I work with. 

 

So when I just open a certain table in the Power Query, I see a number for distinct values in the column "ID". When I remove all but this column, I see a different bigger number for distinct values in the column. When I pick "ID" and 2-3 other columns and remove the rest I get another number for distinct values in the column "ID". I do not apply any filters to the table, just remove columns and get different results. Could you please explain why this happens? It seems the dataset gets larger after just removing columns. Also, this happens to all tables that I work with.

 

Datasource: Amazon Redshift. (OLAP) 

 

Thank you 🙂 

1 ACCEPTED SOLUTION
DOLEARY85
Resident Rockstar
Resident Rockstar

Hi,

 

Power Query by default only shows a preview of the data from the query so it's possible that when you remove columns from a table, that it could affect the cardinality of the remaining columns. 

 

If you're concerned that there is an error somewhere, you could duplicate the input in Power Query, remove the columns in one of the inputs then load to Power BI Report View. You can then do a distict count measure of the ID field from both inputs. I'd imagine they would be the same.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

View solution in original post

3 REPLIES 3
DOLEARY85
Resident Rockstar
Resident Rockstar

Hi,

 

Power Query by default only shows a preview of the data from the query so it's possible that when you remove columns from a table, that it could affect the cardinality of the remaining columns. 

 

If you're concerned that there is an error somewhere, you could duplicate the input in Power Query, remove the columns in one of the inputs then load to Power BI Report View. You can then do a distict count measure of the ID field from both inputs. I'd imagine they would be the same.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Thanks! It worked. Do you by chance have any links for further reading regarding the topic? It's just I never encountered this "issue" before. 

Unfortunately nothing specific about the issue but you could try the Power query documentation, there should be something related to data sources, cardinality and query folding which may also account for the issue:

 

https://learn.microsoft.com/en-us/power-query/

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

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!

Top Solution Authors
Top Kudoed Authors