Forum Discussion
Pivot Not Grouping
Hello Experts,
I am trying to group a simple data set.
I have 2 Columns: Deposits and Withdrawals
I need to Group on SWAP.
It doesnt group for some reason.
Do you see why its not grouped?
I assume its not ideal to have 2 columns.
thank you.
Please see attached file.
The problem is that some of the "SWAP " have a space after the word and others do not
I did a Find & Replace to remove the extra space and refresh the table and it worked perfectly:
7 Replies
If creating PivotTable you add data to data model (if you are on Windows)
texts will be trimmed automatically while loading to data model, thus no additional actions are required
Nice tip. thank you!
texts will be trimmed automatically while loading to data model, thus no additional actions are required
I didn't know that. Is this docmented somewhere?
Detlef_Lewin , I found that eventually, have no idea is it documented or not. Perhaps that was from scratch since above works on Excel 2019 as well.
The problem is that some of the "SWAP " have a space after the word and others do not
I did a Find & Replace to remove the extra space and refresh the table and it worked perfectly:
thank you very much. I thought about that and did check some records but I thought it would ignore the extra space so I didnt continue searching teh records for the spaces. This si good to know.
I do see that Sergei responded that if I used the Data MOdel it would have trimmed the extra space automatically. I am using Power Query and I will now trim it automatically but will change the data source as well. thank you much!
As a comment, if load Power Query into data model extra spaces also will be trimmed. However, better to do that explicitly within the query.
