Same SKU - hand typed different

[Tmk123]

Hi, 

Are there any features in Power BI (or Excel) that will allow me to identify the same SKU if it is typed differently?  I could do this by hand but I have 550 lines to go through.  I am trying to sum up the total amount my company has bought for each item.

The example below shows the manufacturer's SKUs (left) and the quantities that we purchased (right).  The goal is to create one line of XL345PC with a total qty of 3,759.  I am aware of "Group By" and that will take care of the first four rows below.  The last three SKUs have native ERP numbers succeeding the manufacturer's number, along with a mixture of characters and spaces.  As far as I know, "Group By" would see those as unique.

I was curious if Power BI had any features to overcome this.  If not, how would you approach this?  Thank you!!  

XL345PC	149
XL345PC	62
XL345PC	228
XL345PC	108
XL345PC - 9505084	2160
XL345PC 9505084	708
XL345PC/9505084	344

[Vijay_A_Verma · Accepted solution]

Use this step. Replace #"Changed Type" with your previous step

= Table.Group(Table.TransformColumns(#"Changed Type",{"SKU", each Text.BeforeDelimiter(_&" ", List.Difference(Text.ToList(_&" "), {"0".."9","A".."Z","a".."z"}){0})}), {"SKU"}, {{"Total Quantity", each List.Sum([Quantity])}})

[m_dekorte · Accepted solution]

Hi @Tmk123,

Or maybe just create a group key... something like:

Table.AddColumn( PrevStep, 
    "GroupKey", 
    each List.First( 
         List.Select( Text.SplitAny([SKUcolumn], " /-"), 
            each Text.Length(_)>1 
         )
    )
)

I hope this is helpful

[Vijay_A_Verma · Accepted solution]

Use this step. Replace #"Changed Type" with your previous step

= Table.Group(Table.TransformColumns(#"Changed Type",{"SKU", each Text.BeforeDelimiter(_&" ", List.Difference(Text.ToList(_&" "), {"0".."9","A".."Z","a".."z"}){0})}), {"SKU"}, {{"Total Quantity", each List.Sum([Quantity])}})