Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
munk
Helper I
Helper I

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?

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

8 REPLIES 8
AlexisOlson
Super User
Super User

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.

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! @skeets20 @AlexisOlson 

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

  

 

 

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

 

skeets20_0-1637027555067.png

skeets20_1-1637027715142.png

 

skeets20_2-1637027747114.png

 

skeets20
Resolver I
Resolver I

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

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

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

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.