Product ids of varying length

[Chitra_vispute]

I have product ids of varying length includes numeric and alphanumeric value. For eg 0000000000004210182 or 000000000000456789 or 00000000000004567 or Dumbbc or ED56774 how to get actual product ids with removing zero and entire alphanumeric code in power query. Thank you

[grazitti_sapna]

@Chitra_vispute Please follow below steps:

1. Load the Data in Power BI with ProductID column and click on Transform data, it will open Power BI Query Editor.
2.In Power Query → Home tab
Click Use First Row as Headers → select Use First Row as Headers
3.If that option is greyed out or the wrong row becomes headers, go to:
Home → Remove Rows → Remove Top Rows → 1 (to remove the first header row)
4.In Power Query Editor, go to
Add Column → then click Custom Column
and add calculation:
= let
txt = Text.Trim([ProductID]),
// Keep only numbers
digitsOnly = Text.Select(txt, {"0".."9"}),
// Remove leading zeros
cleaned = Text.TrimStart(digitsOnly, {"0"}),
// Return blank if nothing left
result = if cleaned = "" then null else cleaned
in
result
5. Now Power query Editor, Click on Close & Apply .
6. On desktop , you will see the below ProductIDs removing zeros and alphanumeric values placed in new column.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!

[v-nmadadi-msft]

Hi @Chitra_vispute 

May I check if this issue has been resolved? If not, Please feel free to contact us with more details related to the error you are running into as requested by @m_dekorte  if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @Chitra_vispute 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[Chitra_vispute]

Hi Team, 

I tried to use above code but it not resolved yet.  Pls help.  Thanks. 

[m_dekorte · Accepted solution]

@Chitra_vispute could you share more details so we can help?
What steps have you taken so far, what issue are you running into? Screenshots or any specifics would be super helpful in providing support.

[m_dekorte]

1. Create a new blank query
2. Rename the new query: noLeadingZero
3. Open the Advanced Editor and replace all you see there with this:

let
    noLeadingZero = (productID as nullable text) as text => 
        Text.Upper(Text.TrimStart(productID ?? "","0"))
in
    noLeadingZero

For each table that needs the product ID cleaned, follow these steps

1. Go to the 'Add Column' tab
2. Choose 'Invoke Custom Function'
3. In the dialog that shows up:
   • Give your new column a name
   • Choose noLeadingZero as Function Query
   • Choose Column > select the column name with product IDs from the drop down

[v-nmadadi-msft]

Hi @Chitra_vispute ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[MasonMA]

It's a bit unclear in the question. If this is about removing the entire alphanumeric codes beside trimming the leading 0s, try this M code. 

let
    Source = table,
    Numeric = Table.SelectRows(
        Source,
        each Text.Remove([ProductID], {"0".."9"}) = ""
    ),
    Custom1 = Table.TransformColumns(
        Numeric,
        {{"ProductID", each Text.TrimStart(_, {"0"}), type text}}
    )
in
    Custom1

If it is only about trimming the leading 0s, this M code should work. 

let
    Source = table,
    Custom1 = Table.TransformColumns(Source, 
        {
            {
                "ProductID",
                each if Text.Remove(_, {"0".."9"}) = "" 
                     then Text.TrimStart(_, {"0"}) 
                     else _,
                type text
            }
        }
    )
in
    Custom1

[Chitra_vispute]

Above M code is not working using custom column

[MasonMA]

The earlier one is meant to perform transformation directly on the ID Column with Table.TransformColumns function. 

If with Custom Column, consider also adding Text.Upper() to normalize alphanumeric code so that everything lines up under one consistent key. 

The entire M code UI generated is 

let
    Source = table,
    Custom1 = Table.AddColumn(
        Source,
        "ProductID_Cleaned",
        each if Text.Remove([ProductID], {"0".."9"}) = "" 
             then Text.TrimStart([ProductID], {"0"}) 
             else Text.Upper([ProductID]),
        type text
    )
in
    Custom1

[ronrsnfld]

If you just want to remove the leading zero's:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkAGJkaGBoYWRkqxOqgSJqZm5haWGMJgcbCoS2luUlIymOnqAhQ0N1GKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t]),
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ID", type text}}),
    #"Add Real ID" = Table.AddColumn(#"Changed Type","Real ID", each Text.TrimStart([Product ID],"0"), type text)
in
    #"Add Real ID"

If you want something else, please show exactly what you want with the data you have posted.

[Chitra_vispute]

 Above code is not working

[ronrsnfld]

Then there is an issue with how you applied what I provided to your actual data set. You will need to provide information as to both for me to assist. (as text or a sample file, not as a screenshot).

[Chitra_vispute]

Thanks for reply. In dataset extracted from Sap source there are 5 tables all contains Product Ids. In 4 tables Product Ids are of 18 digit code and alphanumeric code of varying length. Remaining one table has Product Ids without leading zero for eg 4210188 or 34678 and also Alphanumeric code for eg DUMBBC or ED45678 or CE566789 varying length. Need help how to align product ids in 4 tables. 

[Chitra_vispute]

In dataset taken from Sap there are 5 different tables has Product Ids . In four tables Matl key is with 18 digit code with varying length and alphanumeric text data with varying length. The remaining one table has product ids without leading zero for eg 4218012 and alphanumeric value for eg Dumbbc or ED45567. This will be my primary to connect with different tables. How to create actual products ids in other tables.