Complicated Merge in Power Query

[munk]

I have an index with different length serial identifiers and I want them to be merged with full length serials. Explanation and examples below.

My reference index looks like this (just 4 cases as example)

CODE         PRODUCT

CHAB            Apple

DDDA            Pear

64                  Egg

71                 Guava

My report table looks like this

SPECIFIED CODE              PRODUCT           (what I want) PRODUCT

CHAB58402834                                                       Apple

CHAB90905940                                                       Apple

DDDA05930256                                                       Pear

DDDA69789857                                                       Pear

642736                                                                     Egg

648294                                                                     Egg

716845                                                                    Guava

What custom column do I need to create in power query (formula wise) to get the PRODUCT column to be filled in based on the first 4 letters of the alphabetic serials and first 2 letters of the numeric serials?

[AlexisOlson · Accepted solution]

Create a custom column in the query editor that checks the first character to see if it's a digit and then take 2 or 4 characters depending on the result.

if Text.Contains("0123456789", Text.Start([SPECIFIED CODE],1))
then Text.Start([SPECIFIED CODE],2)
else Text.Start([SPECIFIED CODE],4)

Then you can do a normal merge using this new column.

[munk]

Thank you for your help! Unfortunately, I am receiving an error on all of the SERIALs that start with numbers.

All of the errors contain a message:

Expression.Error: We cannot convert the value 644682 to type Text.
Details:
Value=644682
Type=[Type]

FIXED: I had to change type back when I first loaded my data in. Thank you so much for the help! @Anonymous @AlexisOlson 

[Anonymous]

Please try this option instead, or it's possible you need to set the datatype of your calculated column from "Any" to "Text"

Text.From( try Number.FromText(
 Text.Start([SPECIFIED CODE],2)
)
otherwise Text.Start([SPECIFIED CODE],4)
)

[Anonymous]

@munk ,  @AlexisOlson solution works great. Unfotunately, PowerQuery doesn't have a straightforward function like ISTEXT or ISNUMBER.  there are many other ways to accomplish though like @AlexisOlson demonstrated.

[Anonymous]

Could you fill your Product column with desired output for reference? 

[munk]

The desired output is already in the post. Where I mention my report table I inserted a column called (what I want) PRODUCT

[Anonymous]

I saw that but I was confused by the "first 2 letters of the numeric serials"  some of examples of what you expected the results to look like in the Product column would help ensure you are helped correctly the first time around.  @AlexisOlson may have understood your request better then I and provided a solution

[munk]

Yes, the first 2 letters for the index serials with numbers in them will always be the same. The numbers afterwards are unique to each specific product purchased. Where the first 2 numbers are the identifiers. Thank you for putting out that confusing bit! Thank you for reaching out and trying to help. I'll definitely see what Alex Olson recommended.