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

[NickolajJessen]

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

[AlB · Accepted solution]

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 

[NickolajJessen]

That was perfect, thank you so much 😁

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