Forum Discussion
Power Query Deletes Customers With Letters
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.
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.