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

 

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.

8 Replies

  • 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

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

          

         

         

    • 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.

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • 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