Creating a Local ID in Power Query

Creating a Local ID in Power Query (Daily Transaction Number)

When working with large fact tables one of the first pieces of advice is to remove ID columns. By definition they have a unique value for each and every row which means they offer no compression whatsoever. The problem is that if you get rid of them, you will often introduce duplicate records into your data. These duplicates aren’t errors, they are just rows with identical sets of data because there is no ID column with which to tell them apart. This can introduce errors when using revisers over these tables but perhaps more importantly, when your client asks to see the individual records in a visual, you won’t be able to do it. Local IDs create a solution where you create a new ID column whose values are only unique to that transaction’s particular day (or even hour). This means lots of repeating values which means great compression AND the ability to show individual records to end users. In this video Brian Grant will walk you through just how easy these can be to add.

Click here to download the companion files

Brian Grant

Brian Grant

Brian Grant is a masterful Senior Analytics Consultant by day, and a Power BI legend by night. A passion for Power BI drove him to holistically master the tool from all three perspectives: M, DAX, and the visualization layer. Brian is a constant teacher who leads Power BI training sessions, while sharing his wealth of expertise through video and blogging resources.
Share on twitter
Share on facebook
Share on linkedin
Share on email


Subscribe to CSG. We’ll supply your inbox with the latest blog posts, training videos, and upcoming events.

Ready to wrangle your data?