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.
