Introduction
As a data consultant, I am often approached by colleagues who need help with their Power BI solutions. This case is rather straightforward and does not need a lengthy introduction so let’s dive right into it.
The Problem
The client needed to show duplicate values in a table visualization without having a unique identifier. In Power BI, this is not possible. Let’s say you create a table like this
Once you remove the Fruit_ID column from the table visualization, Power BI will summarize the table like this;
Honestly, I can’t blame the Power BI developers for this as it is a reasonable automatisation. I mean, why would you want to show duplicate values if there is no unique identifier? But of course, there can always be real-world scenarios in which this is a requirement. As my colleague was faced with this requirement, I got creative and tried to help him.
The Solution
I immediately thought of two different solutions. The first thing I thought of was to add and hide the index column. By doing this, the duplicates would still be visible without having to alter the data. However, it could look better. The hidden column must be as wide as your longest ID (5 digits in this case), you are limited to the colors you can use, etc.
So on the second solution; making the non-unique data unique. The idea here is to add trailing spaces to the duplicate records. To go from this:
- Apple
- Apple
- Apple
To this:
- Apple_
- Apple_ _
- Apple_ _ _
Of course, the underscore would be whitespace.
To implement this solution we would need both Power Query and DAX. Let’s first start by Power Query.
Power Query
Even though you could technically build the whole solution in DAX, the Microsoft documentation advises always to do ETL as close to the source as possible. So whatever we can do in Power Query, we will do so. In case you can complete these steps in your database using SQL or another query language it would be even better.
Step 1: Use Group By:
Step 2: Add a Custom Column
Add a custom column from the following expression:
Table.AddIndexColumn([GroupBy],”DuplicateIndex”,1,1)Step 3: Expand the custom column
You now have an extra column that indicates how many trailing spaces we can add. (The “GroupBy” column is no longer needed and can be removed).
The last step would be to add the trailing spaces. However, when adding trailing spaces in Power Query, they will be automatically removed while loading the data. So if we want to have trailing spaces in our model, we must rely on DAX.
DAX
Fortunately, not much DAX is left to write as all the preparations have been completed in Power Query. By now, you should have a new column containing an integer stating how many trailing spaces should be added.
Using that column, we will create a new calculated column from the following expression.
FruitDuplicates = FruitTable[Fruit] & REPT(“ “, FruitTable[DuplicateIndex])There you have it. You now have a column with unique values that look like duplicates to the human eye. All that is left is to replace the values in your table visualization and voila, your “duplicate” values are now showing.
Conclusion
Another Power BI hack and another happy client :). As you can see here — and in many of my other posts — even though Power BI is limited, with some creativity one can often build a lot more than you can imagine. With a combination of some M (Power Query) and DAX, we managed to solve an issue that not only this client, but many others in the community have requested.
Let me know in the comments if it helped you too.
About me: My name is Bruno and I work as a data consultant. Check out the other stuff I built like a Mumble Rap Detector or connect with me via my website: https://www.zhongtron.me
