Custom Column Formula Help

[rpinxt]

Ok so I made this Custom Colum in Query Editor:

Now this works just fine however I have 1 extra requirement.

So where it takes the first 2 characters of [Material] in 90% of the cases these are letters.

However there are a few materials that start with numbers.

How can I ammend this formula:

=if [Mat.Type] = "ZRAW" then Text.Start([Material],2) else "X"

So that when the Materials starts with a number (any number) it returns "LB" and for all the others just the 2 first characters of the Material.

[Anonymous · Accepted solution]

Hi @rpinxt ,

Please try this formula:

if [Mat.Type]="ZRAW" then try Number.From(Text.Start([Material],2)) otherwise "LB" else "X"

PS: I helped you move your post to the Power Query forum. Posting in the correct forum next time will help you solve the problem faster.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

Sure, you can do it without adding the column.

Personally I divide and conquer when attacking a problem, it's easier to debug and separate out parts of the logic.

One thing about Power Query steps is that you can add columns to help, get other results and then remove the helper columns.

[rpinxt]

I understand but in a large dataset would adding all these extra columns not lead to worse performance (and  I do understand that complex formulas would do the same but still)

Also...how would that formula look 😊....because there does not seem to be an IFERROR formula othere then try / otherwise....but that would not fit very good in my if formulas.....

[HotChilli]

You could find out whether the Material starts with a number by adding a column using the 

Number.FromText(  ,,) function.  You can pass the first character of Material to this function (which you already know how to do using Text.Start.)

This will show error or a number.  
If you want to make it a bit neater, wrap it with try..otherwise to handle the error gracefully.

Once you have this column working you can implement the logic you desire in the final column : for example,

if there is a number then "LB" else first two chars.

----

See if you can progress this and I'll help if you have problems

[rpinxt]

Ok this sounds interesting @HotChilli .

But would I really need an extra column?

Could I not add the part of Number.FromText to this formula?

=if [Mat.Type] = "ZRAW" then Text.Start([Material],2) else "X"

Something like =if [Mat.Type] = "ZRAW" then
                            Iferror(Number.FromText([Material]) then "LB" else
                            Text.Start([Material],2)

                            else "X"

Or would that not work because of the double If and double else?