Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
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.
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
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
126 | |
110 | |
87 | |
70 | |
66 |