Forum Discussion
Group By fx in PBI Power Query does not group all duplicates as with Excel Power Query.
All - I have been doing some research on a Group By issue and have yet to find a solution...In Power BI, when performing the Group By function, I find that not all the duplicate values are uniquely grouped with corrsponding counts as they do in Power Query (Excel). My thinking is that performing the same function with the same data would produce the same result in both PBI and Excel.
Curious if anyone here has ever run into this scenario and knows of a solution in PBI that will provide the expected outcome.
Any help here would be greatly appreciated.
Hi jzadok,
Thanks for sharing your custom trim function, that is a great approach. If you are still seeing differences, it is usually due to hidden characters or casing that Excel strips but Power BI keeps. You can try wrapping your column with:Text.Upper(Text.Clean(Text.Trim([YourColumn])))This should standardize spaces, remove hidden characters, and align grouping with what you see in Excel.
Let us know if that works out. Let us know if you have any doubts regarding this. We will be happy to help.Thank you for using the Microsoft Fabric Community Forum.
9 Replies
- FBergamaschi
Super User
Your thinking is right as Power Query is the same in both Excel and Power BI
Can you please share your input file so I try to reprodice the problem?
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
ronrsnfldThat does not happen unless there is something different about your data (or possibly or code) in the two versions. If you could share the *.pbix file that demonstrates the non-grouping (along with the data source if it is separate from the .pbix file), I may be able to assist.
- tayloramy
Hi jzadok,
Can you please provide sample data so we can reproduce the problem and solve it?
See How to Get Your Question Answered Quickly - Microsoft Fabric Community
and How to provide sample data in the Power BI Forum - Microsoft Fabric Community
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
- audreygerred
Can you please share the data that you are using? Power Query behaves the same in Excel, Power BI and Dataflows.
- wdx223_Daniel
Community Champion
maybe there are some unseeable charactor. try this code in your PBI PQ editor
=Table.Group(YourTable,"name",{"Count",Table.RowCount},1,(x,y)=>Vaule.Compare(Text.Clean(x),Text.Clean(y)))
- v-kpoloju-msft
Community Support
Hi jzadok,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to wdx223_Daniel, audreygerred, tayloramy, ronrsnfld, for his inputs on this thread.
I can understand why it feels confusing to see Group By behave differently between Excel and Power BI, especially when you are expecting identical outcomes with the same data.
The difference comes down to how Power BI handles text values under the hood. In Power BI, values that look identical may still contain small differences (for example trailing spaces, hidden/non-printable characters, or even variations in letter casing). These subtle differences can prevent Group By from combining the rows, while Excel tends to normalize them more aggressively, which is why you are seeing a mismatch.
To align Power BI with Excel, you can clean and standardize your column before applying Group By. A few quick transformations in Power Query usually solve it:
• Trim → removes leading/trailing spaces
• Clean → removes hidden/non-printable characters
• Format (Lowercase/Uppercase) → ensures consistent casing if needed
Refer these links:
1. https://learn.microsoft.com/en-us/powerquery-m/text-trim
2. https://learn.microsoft.com/en-us/powerquery-m/text-clean
3. https://learn.microsoft.com/en-us/powerquery-m/text-functions
4. https://learn.microsoft.com/en-us/power-query/power-query-uiOnce you apply these steps and then run Group By, your results should match what you see in Excel.
Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
Thank you for the input. I nest a trim function/parameter (see below) in Table.TransformColumns as a general rule of thumb and best practice before working with the data.
let
Source = (InputText as text) =>let
Trimdata = Text.Combine(
List.Select(
Text.Split(
Text.Trim(InputText), " "),
each _ <> ""
),
" "
)in
Trimdata
in
Source- v-kpoloju-msft
Hi jzadok,
Thanks for sharing your custom trim function, that is a great approach. If you are still seeing differences, it is usually due to hidden characters or casing that Excel strips but Power BI keeps. You can try wrapping your column with:Text.Upper(Text.Clean(Text.Trim([YourColumn])))This should standardize spaces, remove hidden characters, and align grouping with what you see in Excel.
Let us know if that works out. Let us know if you have any doubts regarding this. We will be happy to help.Thank you for using the Microsoft Fabric Community Forum.
