Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hello,
I have imported a number of Excel files (all within the same folder), using the binary/combine files approach. Each of these files is a "snapshot" in time of opportunities from our CRM. The problem is that each file contains rows that the other files have as well, so I'm ending up with tons of duplicates. And it's not as easy as just "removing duplicates".
When you import data like this, Power Bi creates a column called Source.Name. You can see an example below. There are files all the way thru May (only 1-13-20 and 1-20-20 are showing). I am only concerned with the earliest file from each month. The file with the 1-13-20 date in the case of January. For April the file date is 4-6-20.
A simple way to look at this is that I could go into the Excel file for January 13 2020, go to the column called Est Close Date, and delete all of the dates that did not fall within January 2020. Rinse and repeat for every file. Then load into Power Bi.
Surely there is an elegant approach to this using the query editor or DAX?
Solved! Go to Solution.
If you want to keep just the earliest file in each month, you can use the approach in this post to add an index column that restarts with each month. If you don't have a good column to group by, create one off of your file date (add Month column).
1. Sort by file date descending
2. Group By your month column, and keep "All Rows" instead of calculating an aggregation
3. Add an index while each month is grouped in Table form
4. Expand the grouped tables
5. Filter to where the nex subgroup index value = 1
If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
https://www.myonlinetraininghub.com/numbering-grouped-data-power-query
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous ,
Or do like this.
1. Split Column 'Source.Name' by Delimiter.
2. Group rows.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Or do like this.
1. Split Column 'Source.Name' by Delimiter.
2. Group rows.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you want to keep just the earliest file in each month, you can use the approach in this post to add an index column that restarts with each month. If you don't have a good column to group by, create one off of your file date (add Month column).
1. Sort by file date descending
2. Group By your month column, and keep "All Rows" instead of calculating an aggregation
3. Add an index while each month is grouped in Table form
4. Expand the grouped tables
5. Filter to where the nex subgroup index value = 1
If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
https://www.myonlinetraininghub.com/numbering-grouped-data-power-query
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
103 | |
68 | |
47 | |
39 | |
37 |