Forum Discussion
Group by Columns malfunction
Hi ELIU ,
It’s interesting that sorting by Project Name before applying Group By resolves the issue. This suggests that the problem is related to how Power Query processes the data when it’s unsorted. One possible explanation is that if your data source is a live database, API, or an Excel file that gets refreshed frequently, the order of rows may change with each refresh. Some transformations in Power Query, such as Merges, Expands, or Joins, can cause row order to change, which might lead to unintended duplications in aggregation if Power Query processes rows differently each time.
Another possibility is that Power Query applies implicit indexing when processing data, and when the rows are unordered, the Group By step may treat some values inconsistently. Sorting ensures that identical Project Name + Project ID + Project Date records appear consecutively, avoiding any discrepancies. Additionally, hidden formatting issues may be causing unintended duplicates. Even if the values appear the same, they may have leading/trailing spaces, data type mismatches, or invisible characters. Sorting could be forcing Power Query to normalize how it reads these values, making it treat them consistently. You can remove any hidden inconsistencies by applying a Trim & Clean step before grouping:
= Table.TransformColumns(Source, {{"Project Name", Text.Trim, type text}})It’s also worth considering that Microsoft occasionally updates Power Query’s internal optimizations, and if a recent update modified how it processes Group By operations on unordered data, that might explain why the behavior changed suddenly. To prevent this issue in the future, you can keep sorting Project Name before applying Group By and ensure that all grouping columns have consistent data types (Text, Number, Date). If your data comes from an external source, checking for any recent structural changes might also help in identifying the root cause.
Best regards,
Hi ELIU ,
It looks like the issue is caused by duplicated rows or an unintended multiplication effect happening before the Group By step. To troubleshoot, first, check the raw data for duplicate combinations of Project Name, Project ID, and Project Date. Sorting the data and using the Remove Duplicates function on these columns before grouping might help. Another possibility is that a Merge (Join) step in the query is introducing duplicates. If a many-to-many relationship exists, it could be causing the Expense column to be summed multiple times. Go to the Applied Steps pane and review each transformation carefully to see where the duplication starts.
If filtering by a specific Project before grouping gives the correct total, the issue likely occurs when multiple projects are processed together. A test you can try is to group the data without the Project Date to see if the totals remain consistent. Additionally, in the Group By step, confirm that you're using the Sum function for Expense and not selecting All Rows, which might cause the data to be repeated.
To verify whether the Group By step is summing the correct values, you can add a custom column with the following Power Query formula:
= List.Sum(Expense)
If this sum does not match the grouped total, it indicates that some transformation step is causing row duplication. Another useful check is to count the rows before and after applying Group By with:
= Table.AddColumn(Source, "Row Count", each 1, Int64.Type)
Summing this column before and after grouping can help determine if there are extra rows. If you're using a Merge Queries step, make sure the join type is correct—Left Join is usually safer than Full Join or Inner Join, which might duplicate rows unexpectedly.
If the issue persists, sharing the M Code for your transformation would help pinpoint the exact cause.
Best regards,
DataNinja777 Thank you so much for your detailed step by step analysis. I've actually figured out that if I sorted the data/table by Project Name before applying the Group by step, the issue is gone. But I still couldn't figured out why is such behavior and why before I made any changes to the data it worked fine before.
Hi ELIU ,
It’s interesting that sorting by Project Name before applying Group By resolves the issue. This suggests that the problem is related to how Power Query processes the data when it’s unsorted. One possible explanation is that if your data source is a live database, API, or an Excel file that gets refreshed frequently, the order of rows may change with each refresh. Some transformations in Power Query, such as Merges, Expands, or Joins, can cause row order to change, which might lead to unintended duplications in aggregation if Power Query processes rows differently each time.
Another possibility is that Power Query applies implicit indexing when processing data, and when the rows are unordered, the Group By step may treat some values inconsistently. Sorting ensures that identical Project Name + Project ID + Project Date records appear consecutively, avoiding any discrepancies. Additionally, hidden formatting issues may be causing unintended duplicates. Even if the values appear the same, they may have leading/trailing spaces, data type mismatches, or invisible characters. Sorting could be forcing Power Query to normalize how it reads these values, making it treat them consistently. You can remove any hidden inconsistencies by applying a Trim & Clean step before grouping:
= Table.TransformColumns(Source, {{"Project Name", Text.Trim, type text}})It’s also worth considering that Microsoft occasionally updates Power Query’s internal optimizations, and if a recent update modified how it processes Group By operations on unordered data, that might explain why the behavior changed suddenly. To prevent this issue in the future, you can keep sorting Project Name before applying Group By and ensure that all grouping columns have consistent data types (Text, Number, Date). If your data comes from an external source, checking for any recent structural changes might also help in identifying the root cause.
Best regards,
