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.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
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

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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)
)