Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
4 years ago
Solved

Custom Column Formula Help

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 a...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
HotChilli
Community Champion
4 years ago

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's avatar
    rpinxt
    Solution Sage
    4 years ago

    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?