Forum Discussion
Power Query Deletes Customers With Letters
I have four customer numbers that I want to group together. The numbers can contain letters. Power BI deletes the customers that contain the letters and I can only see sales for customers with numbers. How can I combine these? I have tried turning my data into text but that isn't helping.
Hi Sbegnaud,
Thank you for reaching out in Microsoft Community Forum.
Please follow below steps to Keep Customer Numbers with Letters in Power BI;
1.In Power Query Editor, select the customer number column and change its data type to Text.
2.Remove any hidden spaces or special characters by trimming or replacing them (e.g., use Text.Trim).
3.Ensure the customer number column in your source system (Excel, database, etc.) is formatted as text, not numeric.
4.After applying these changes, refresh your Power BI data load.
Please continue using Microsoft Community Forum.
If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.
Regards,
Pavan.
6 Replies
If you've already converted the column to text and Power BI is still deleting customers with letters, try the following steps:
- Check for Errors: In Power Query, ensure there are no errors in the column by selecting "Transform" > "Detect Data Type" again.
- Remove Special Characters: Sometimes, special characters or hidden spaces might cause issues. You can use the "Transform" > "Replace Values" option to remove any non-numeric characters or unwanted spaces.
- Use Custom Column: Create a new column to explicitly handle the grouping. Use a formula like Text.Trim([CustomerNumber]) or Text.Select([CustomerNumber], {"0".."9","A".."Z"}) to clean up and standardize the values.
- Group by Customer ID: After these adjustments, try grouping the data again, ensuring you select the newly formatted column.
Hi Sbegnaud,
Thank you for reaching out in Microsoft Community Forum.
Thank you BhavinVyas3003 for the helpful response.
As suggested by BhavinVyas3003, I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.
Please continue using Microsoft community forum.
Regards,
Pavan.Hi Sbegnaud,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" and give a 'Kudos' so other members can easily find it.
Thank you,
Pavan.I ended up grouping them together before I brought into Power BI.
Hi Sbegnaud,
Thank you for reaching out in Microsoft Community Forum.
Please follow below steps to Keep Customer Numbers with Letters in Power BI;
1.In Power Query Editor, select the customer number column and change its data type to Text.
2.Remove any hidden spaces or special characters by trimming or replacing them (e.g., use Text.Trim).
3.Ensure the customer number column in your source system (Excel, database, etc.) is formatted as text, not numeric.
4.After applying these changes, refresh your Power BI data load.
Please continue using Microsoft Community Forum.
If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.
Regards,
Pavan.
Hi Sbegnaud,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" and give a 'Kudos' so other members can easily find it.
Thank you,
Pavan.
