Forum Discussion
Unifying different values in rows
Hi - I have some messy data, relating to web page traffic that I'm trying to clean up. The data has been imported from a few monthly reports, which hold cumulative data for page views, along with a 'FirstPublished' date for each page. Unfortunately, for reasons beyond my control, these reports contain some duplicate page entries, but with different 'FirstPublished' dates.
| SourceReportMonth | PageTitle | Views | FirstPublished |
| July | Page1 | 3000 | 2021-11-31 |
| July | Page1 | 3000 | 2022-10-03 |
| July | Page1 | 3000 | 2020-01-01 |
| August | Page1 | 3123 | 2021-11-31 |
| August | Page1 | 3123 | 2022-10-03 |
| September | Page1 | 3550 | 2021-11-31 |
| September | Page1 | 3550 | 2022-10-03 |
(there are multiple different pages shown within each report, but I removed them here for clarity and just shown Page1)
I somehow need to be able to remove duplicates, but whilst ALSO making the FirstPublished consistent for each Page (ideally showing the earliest date, but that's not essential as long as they're all the same):
| SourceReportMonth | PageTitle | Views | FirstPublished |
| July | Page1 | 3000 | 2020-01-01 |
| August | Page1 | 3123 | 2020-01-01 |
| September | Page1 | 3550 | 2020-01-01 |
Any suggestions would be gratefully received!
Thanks
I've found a way using this calculated column which seems to be providing the desired output:
UnifiedPublishedDates = CALCULATE( MIN( 'myDataTable'[FirstPublished]), FILTER('myDataTable','myDataTable'[PageTitle]=EARLIER('myDataTable'[PageTitle]) ) )
3 Replies
I've found a way using this calculated column which seems to be providing the desired output:
UnifiedPublishedDates = CALCULATE( MIN( 'myDataTable'[FirstPublished]), FILTER('myDataTable','myDataTable'[PageTitle]=EARLIER('myDataTable'[PageTitle]) ) )Hey there ryoba,
One method that you can use to do this is by sorting your First Published column ascending in Power Query. Then control selecting the SourceReportMonth and PageTitle column and doing a normal remove duplicates. This will not remove the first occurence of that data (hence the sort by), but remove the duplicates that you are concerned about.Many thanks d_rohlfs - that does keep the first date, but unfortunately doesn't fix the issue of the dates still being different for the different months. I end up with something like this, but I need all of the items in the FirstPublished column to be the same as each other:
eReportMonth PageTitle Views FirstPublished July Page1 3000 2020-01-01 August Page1 3123 2021-11-31 September Page1 3550 2021-11-31
