How to remove duplicates from individual powerquery columns without removing entire rows

keithlearn

New Member
Joined
May 22, 2019
Messages
2
[COLOR=#BBC0C4 !important][COLOR=#6A737C !important]0
<button class="js-vote-down-btn grid--cell s-btn s-btn__unset c-pointer" title="This question does not show any research effort; it is unclear or not useful" aria-pressed="false" aria-label="down vote" data-selected-classes="fc-theme-primary" style="margin: 2px; box-sizing: inherit; font: inherit; position: relative; padding: 0px; border-width: initial; border-style: none; border-color: initial; border-radius: 3px; background-image: none; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; outline: none; box-shadow: none;"><svg aria-hidden="true" class="svg-icon m0 iconArrowDownLg" width="36" height="36" viewBox="0 0 36 36"></svg>
</button><button class="js-favorite-btn s-btn s-btn__unset c-pointer py8" aria-pressed="false" aria-label="favorite" data-selected-classes="fc-yellow-600" title="Click to mark as favorite question (click again to undo)" style="margin: 0px; box-sizing: inherit; font: inherit; padding: 0px; position: relative; border-width: initial; border-style: none; border-color: initial; border-radius: 3px; background-image: none; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; outline: none; box-shadow: none;"><svg aria-hidden="true" class="svg-icon iconStar" width="18" height="18" viewBox="0 0 18 18"></svg>

</button>
[/COLOR]
[/COLOR]
I have a data table that records cost savings data and I have 1 row per project. This has overall project type data such as annual spend, annual savings, etc. but also has the months the savings fall into. To pivot on this data, I converted it to a table with PowerQuery but some columns repeat such as annual spend for each month where there are savings so I might get 10 rows for savings which is correct, but the annual spend is duplicated 10 times. Can I remove duplicates in just those columns retaining the other data.
I have searched and tried various solutions but haven't found one that works. I am not set on data table format, so am open to anything.
Below is a sample of the data

Sample of PowerQuery

As you will see, Baseline Spend, Negotiated Spend, Savings Amount are all shown for each row and I need to use these in a pivot/slicer.
Any help would be appreciated.
Regards,
Keith

 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Cross posted https://stackoverflow.com/questions...idual-powerquery-columns-without-removing-ent

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
I don't fully understand the question. Can you write it differently?

Some techniques you might leverage include:

* Filter out null value on baseline spend
* Group by Project Name and Baseline spend to get a unique list. Specify min date or max date. This will give you the first or last occurrence of a spend. Ditch all the other data.
* Unpivot Baseline Spend, Negotiated Spend, Savings Amount to bring them into one dimension from separate dimensions
* Put indexes on everything and you can merge back into your source data if you want
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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
Back
Top