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

Share This:
Twitter
Facebook
LinkedIn

Stay up to date with the latest customer data news, expert guidance, and resources.

Iteration and Error Handling in M Query

All iteration roads in M Query eventually lead to the List.Generate function. In strengthening our data manipulation skills through Power Query, we absolutely need this looping function.

More Resources

Trusted By Over 6 Million End Users​

Industry leaders and over 6 million end users choose SkyPoint as their comprehensive and compliant Modern Data Stack Platform. A Gold Certified Microsoft Partner, SkyPoint Cloud turns siloed customer data into connected customer experiences.