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!

 

[ExcelAtEverything]

So, I ended up just plugging your new step in right after Source step, since I don't see an initial Changed Type, other than the one that appears midway down, but pretty sure that one was manual. Anyway, it seems to work perfectly! Thanks, this is a tremendous help to me Alex!

 

[ExcelAtEverything]

That didn't work after Source step, so I moved it to right after that "changed type" in the middle of my step stack, which was right after my "expanded table column". Looks like it has to happen after that part where I expanded the table rows. You had no way of knowing that based on the info I gave you, but just in case anyone else is in a similar situation. Thanks again!

 

[Alex Blakenburg]

That didn't work after Source step, so I moved it to right after that "changed type" in the middle of my step stack, which was right after my "expanded table column". Looks like it has to happen after that part where I expanded the table rows. You had no way of knowing that based on the info I gave you, but just in case anyone else is in a similar situation. Thanks again!

Happy you got it working, glad I could help.

 

[ExcelAtEverything]

 

[Alex Blakenburg]

A little background information that might help in the future.
My initial comments were based on having to work from that little table you had in your screenshot.

The way M code works is that each step refers to the previous step.
So if you copy in only part of someone else's code you are likely to have to update the reference to the previous step.
Most steps will be prefixed with a # and then surrounded by quotes.
(Theoretically a step with no spaces doesn't need it but even those ones will still work if you add them.)

The other watch out for inserting code, is that you if you change or remove any column names the subsequent code may not work.
It depends on whether a subsequent step has hard coded that column name in the code.
In this case following the Add Column step with the deleting the old column and renaming the new column with exactly the same name mitigated that.

Have a look at both images below.

This is the code I sent you from my very simple data set.
The step "Added Conditional Column" is what is being displayed in the Formula Bar

The below is just a sample and not your code. It is just to illustrate that each step has as the "first parameter" the name of the previous step.

 

[ExcelAtEverything]

Thanks Alex! That M Code info is GOLD and much appreciated. That was an "a-ha" moment for me! Fortunately I already figured out the part about subsequent steps. I learned that the hard way early on in this project. I ended up just deleting the query and starting again... which caused a ton of other problems additionally. Knowing what I know now, I would have easily been able to fix that in one minute.

So am I to understand then that your code as you sent it either needs to go directly after an appropriate "Changed Type", or the "Changed Type" in your code needs to be replaced with whatever step appears prior to its new position in my query? Or am I misunderstanding?

= Table.AddColumn(#"Changed Type", "Custom", each if not Text.EndsWith([Primary Category], "s") then Text.Combine({[Primary Category], "s"})
else [Primary Category])[/CODE]

 

[Alex Blakenburg]

So am I to understand then that your code as you sent it either needs to go directly after an appropriate "Changed Type", or the "Changed Type" in your code needs to be replaced with whatever step appears prior to its new position in my query? Or am I misunderstanding?

Your understanding is perfect.

Just be mindful of the 2nd watch out, being that if the inserted steps changes a column name or deletes the column all together it may break subsequent code if the column name has been hard coded in a subsequent step.

 

[ExcelAtEverything]

Excellent! Yep. I'm always hyper-aware of that 2nd watch-out since I got burned on it pretty badly early on with PQ. I purposely broke a dozen or so queries by inserting various steps which I knew would break them, and practiced fixing them. ?

 

[ExcelAtEverything]

I really appreciate the way you go the extra mile. Those tips really help.

 

[ExcelAtEverything]

I tried using your logic about the M code referencing the previous step in another thread I have going with a separate issue, but I guess I still don't quite understand it. Lol! I'lll get it in time. Going to keep at it.

Power Query: Show only transactions starting on first FULL month

Hello, I need to know if there is a way to do something in Power Query. Looking at the screenshot of part of my Power Query below, you will see a "Date" column followed by an "Org #" column. There are currently 4 different "Org #'s" (which are basically individual stores), with new stores being...

www.mrexcel.com