Forum Discussion
Grouping column values doesn't work with replaced values
After I do a Replace Values in Power Query, the replaced strings are not recognized when grouping column values. The original strings still show up in the grouping window, while the new strings appear in the legend. As such, grouping does not work on the modified values.
Is there a way to change this behaviour?
Steps to reproduce:
1. Perform Replace Values on a column using Power Query
2. Create a new group using that colum, and add column values to different groups
3. Add the new grouped field to legend in a bar chart
4. Values that were replaced appear outside of groups in the legend
After many unsuccessful fixes, I found out it was because the strings had an invisible character that was messing up the grouping. Even though the values were getting replaced in PQ, they were showing up in their original form in the grouping feature. This extra step in PQ solved the problem:
= Table.TransformColumns(
#"Replaced Value2",
{"column", each Text.Trim(Text.Clean(_)), type text}
)Where "Replaced Value2" is the step where I did my Repace Values, and "column" is the colum with said values.
6 Replies
robroc You might have success by adding a Table.Buffer step after the replacement of the values? Sounds like a strange problem.
PQ follows the steps strictly.
Please Ensure Replace Values Happens Early
Move the Replace Values step as early as possible in your Power Query steps, ideally before any grouping or other transformation referencing that column.
if ths does not work, pls provide some sample data and expected output.
Hi robroc,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to ryan_mayu & GeraldGEmerick for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
After many unsuccessful fixes, I found out it was because the strings had an invisible character that was messing up the grouping. Even though the values were getting replaced in PQ, they were showing up in their original form in the grouping feature. This extra step in PQ solved the problem:
= Table.TransformColumns(
#"Replaced Value2",
{"column", each Text.Trim(Text.Clean(_)), type text}
)Where "Replaced Value2" is the step where I did my Repace Values, and "column" is the colum with said values.
