Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Anonymous
Not applicable

Formatting

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?

1 REPLY 1
gpoggi
Responsive Resident
Responsive Resident

Hi @Anonymous ,

 

You can do it in Power Query as well, let's say you have this table:

Table1.jpg

 

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:

Table2.jpg

 

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.