Help with combining similarly named rows in Power Pivot/Query

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
317
Office Version
  1. 2019
Platform
  1. Windows
Hello,

See my abbreviated example screenshot below. I have a pivot table which is showing two very similar items in a row of a dozen or so items, and I would like to combine any of the similar rows (like "Tennis Balls/Tennis Balls). I am noticing in my incoming data that occasionally there is an "s" dropped off the end of one of the items, which is occassionally causing me to have 2 rows of data for the same item. My data is coming from Power Pivot>Data Model>Power Query>Excel file living in a folder which are updated often. Right now it is only happening with 2 different items, each with an extra similar column where the final "s" is left off the name, but maybe it starts happening with another item as well. I'd like to fix this so that I end up ideally with only 1 column by the time my Power Pivot table displays it.

What is the easiest way to fix this? After unsuccessfully trying to fix in Power Pivot, I thought that there must be a way in Power Query to fix it. But if there is, I can't seem to figure it out. In Power Query, this data is a huge list of every item on every sales ticket, for every day for the past 2 years, along with info like sales amount, profit, quantity sold, PRIMARY CATEGORY, etc. The Primary Category info in question is located in a very lengthy column named "Primary Category" which lists the category the item sold which is shown on that row. So it appears just like in the example screenshot below, but MUCH longer. There are only about 12 different categories total that they use, but there are a lot of sales tickets. Thanks!

1620031528309.png
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,116
Office Version
  1. 365
Platform
  1. Windows
How sure are you that every Primary Category is going to finish in an "s" ?
If you are 100% sure then you could get away with this.

  • Load the data into PQ
  • It will create a source and a changed type step
  • Then insert a step
  • Replace the step content with the below which will create an additional column with an "s" added to any category without out one.
  • The remove the old column
  • rename the new the new column and move it to the left.
Power Query:
= Table.AddColumn(#"Changed Type", "Custom", each if not Text.EndsWith([Primary Category], "s") then Text.Combine({[Primary Category], "s"})
 else [Primary Category])

Advantage:
This is very low maintenance

Disadvantage
Only works is the issue is a trailing "s:

If you have multiple differing issues you will need to create a mapping table from possible codes to the standards codes.
(similar concept to what autocorrect uses)
You could do that as a table that loads to PQ or you could possibly create it in the datamodel if it is only to be used for PP.
 
Solution

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
317
Office Version
  1. 2019
Platform
  1. Windows
Hi Alex! While I am positive that every column currently ends in "s" except for the offending ones, I am not positive that will always be the case. But I'm going to run with that for now and just hope I don't have to cross that bridge later. Seems like it will be fine. Could you explain what you mean when you say to load the data into PQ? Do you mean to redo the original query that it's coming from? Or do you mean to create a new one using... what?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,116
Office Version
  1. 365
Platform
  1. Windows
I only had the image you posted to work from. The order given in “My data is coming from Power Pivot>Data Model>Power Query>Excel file” is not the usual order in that it’s normally PQ to Data Model to PP. If the later is the case, you would do it in PQ on the first pass.
If you are happy with a less generic solution you could just replace Tennis Ball with the plural version.
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
317
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

If you are happy with a less generic solution you could just replace Tennis Ball with the plural version.
Won't work because I would need to keep doing that every time I added new updated sales info, which is daily. I keeps popping up on the newer reports that way too.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,116
Office Version
  1. 365
Platform
  1. Windows
Won't work because I would need to keep doing that every time I added new updated sales info, which is daily. I keeps popping up on the newer reports that way too.

If you do a replace in PQ it will do it everytime you refresh.
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
317
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I only had the image you posted to work from. The order given in “My data is coming from Power Pivot>Data Model>Power Query>Excel file” is not the usual order in that it’s normally PQ to Data Model to PP. If the later is the case, you would do it in PQ on the first pass.
Sorry Alex, I wrote that as I was thinking it, which was backtracking from the current location (Power Pivot). Also my arrows look backwards too. Lol. Basically my info starts and goes Excel file>PQ>DModel>PP. Sorry for the confusion. Ok, I'm going to give it a shot.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,116
Office Version
  1. 365
Platform
  1. Windows
If you get stuck send me the details and I will have a look tomorrow.
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
317
Office Version
  1. 2019
Platform
  1. Windows
  • Load the data into PQ
  • It will create a source and a changed type step
  • Then insert a step
Maybe I deleted the initial "Changed Type? Should I just do it after the Source? The column in question isn't even visible in PQ until step 6 "Expanded Table Column1". Does that matter?
Here is what it looks like, currently with no changes made:
1620049644973.png
 
Last edited:

Forum statistics

Threads
1,136,445
Messages
5,675,900
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top