Forum Discussion

Tmk123's avatar
Tmk123
Helper II
2 years ago
Solved

Same SKU - hand typed different

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

XL345PC149
XL345PC62
XL345PC228
XL345PC108
XL345PC - 95050842160
XL345PC 9505084708
XL345PC/9505084344
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    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's avatar
    m_dekorte
    2 years ago

    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

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    2 years ago

    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's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    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