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

Anonymous ,  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.