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
Tmk123
Helper I
Helper I

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
2 ACCEPTED SOLUTIONS
Vijay_A_Verma
Super User
Super User

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])}})

View solution in original post

m_dekorte
Super User
Super User

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

View solution in original post

2 REPLIES 2
m_dekorte
Super User
Super User

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
Super User
Super User

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])}})

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors