Forum Discussion
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 🙂
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 👍
3 Replies
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 👍
