Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi All,
I am working on managing my data in power query and have run into an issue i can not solve. I am working on client info from Open Air and have created a new column called "Unique ID" using the concatenate function combining project name and number. From here i have right clicked on my "project number" column and "replaced values" so that the null items in my column will be replaced with "999999" to follow the 6 digit format. We originally thought all of the values contained 6 digits and have come to find some are 4 and 5 digit numbers. From there noticing formating issues with the formula working for 99% of the project names but the other 1% being dashes in the wrong places due to human error in Open Air. Wondering if there is a DAX formula to fix these two formatting issues?
Hi @Anonymous ,
You can do it in Power Query as well, let's say you have this table:
So just go to Add Column / Custom Column and use Text.PadStart function:
Text.PadStart(Text.From([Project Number]),6,"0") & [Project Name]
You will get an Unique ID like this:
So as you can see it doesn't matter if the project number has < 6 digits, it will complete it with "0" or any character you decide.
Hope this helps.
Regards,
Gian Carlo Poggi
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
7 | |
5 | |
5 |