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

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
NickolajJessen
Solution Sage
Solution Sage

If-formula in M to test for string length and return either column or column + string

I need to correct some errors made in the financial account transactions. (See row 11)

 

Projects should always have 5 numbers followed by "-01"

 

How do i correct this errror in data?

 

I need to make this change in power query, since i aggregate the data later on.

I'm thinking that i need an if-statement in M, that checks the length of the Projekt_ID

 

BI M string.PNG

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Hi @NickolajJessen 

 

Are all Ids without "-01" at the end 5 digits in length? If so, you can create a new custom column like this:

 

= if Text.Length([Projekt_ID]) = 5 then [Projekt_ID]&"-01" else [Projekt_ID]

Otherwise you'd need to explain what patterns are possible as IDs and what the result should be for each one of them

 

Cheers 

 

View solution in original post

2 REPLIES 2
AlB
Community Champion
Community Champion

Hi @NickolajJessen 

 

Are all Ids without "-01" at the end 5 digits in length? If so, you can create a new custom column like this:

 

= if Text.Length([Projekt_ID]) = 5 then [Projekt_ID]&"-01" else [Projekt_ID]

Otherwise you'd need to explain what patterns are possible as IDs and what the result should be for each one of them

 

Cheers 

 

That was perfect, thank you so much 😁

 

I though i already tried that, but i must have made an error along the way

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

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

Top Kudoed Authors