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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Anonymous
Not applicable

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
Not applicable

@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

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors