Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Complicated Merge in Power Query

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

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.