Forum Discussion
How to Easily Locate Duplicates in a Row After Merging Two Datasets ?
Community ChampionI'm not sure how you could easily program that kind of duplicate detection. If you had Amounts and Amounts.1, which is what happens when you merge two tables with the same column names then it wouldn't be too hard. You could use the logic above once you convert the column names to a field. Table.ColumnNames() can do this.
For example, I expanded Date and DateKey from another table in to my Date table and it created duplicates with the .1 after them. Using Group By with the All Rows logic, I can see I have Date and Date.1
But to do that you need a common name - I used the Text.BeforeDelimiter() on the column to get everything before the period. But you cannot do that if your columns are $amount and AMOUNT unless it is consistently like that, and you can get the text after the $ sign, and then upper case all of them. Power Query is case sensitive, so Amount, AMOUNT, and amount are 3 different values. (Those are just one in DAX).
Thanks for clarifying that query is case sensitive. I am pretty new to it, and I have figured out it'd be a little complicated to locate/identify all the duplicates - given that they don't have the same column names.
I guess a follow up question would be what does someone in this situation do in order to identify a large number of duplicates? Going over each and every column manually is not feasible.
- edhans
The key to a well performing model is few columns. The Vertipaq engine in Power BI loves very skinny, very tall tables. If you have too many columns to evaluate the issues with duplicates, then your main problem right now isn't duplicates. It is too many columns period.
I will spend hours (days!) ripping out columns out of a model to get more performance. The more columns you have the worse compression is, the larger your data dictionaries are, etc. I'm working with a model right now I dislike intensely because of how many columns the main fact table has, and it is only 24 columns. I really want to refactor it by doing some pivot operations and make the table taller and narrower, which I can do because of how the measures need to work, but I just need to find the time to do it.