Help with combining similarly named rows in Power Pivot/Query

[ExcelAtEverything]

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!

 

[Alex Blakenburg]

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.

= 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.

 

[ExcelAtEverything]

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]

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]

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]

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]

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]

If you get stuck send me the details and I will have a look tomorrow.

 

[ExcelAtEverything]

Ok thanks so much! ?

 

[ExcelAtEverything]

• 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: