Forum Discussion
How to replace values in a column in power query
I am trying to take only selected columns from my raw data for report which will be automatically updated when I add details in my raw data file.
The raw data file has so many columns including "invoice number" column.
The invoice numbers for Branch 1 is just normal numbers. But the invoice numbers for Branch 2 is suffixed with "-H".
Eg: the invoice numbers are like 1-H, 2-H and so on.
When I tried to use power query to automate, the invoice number column shows error for those with "-H".
The data type of the column is numbers. But for Branch 2, all the invoice numbers are shown as "Error".
I tried to change the data type to text. But doesn't work. I tried to replace the value (from "-H" to blank). Didn't work either.
Requesting to send the steps rather than code.
Please help on the issue.
You had used int64.type for Invoice No which won't work for alphanumeric data type. The same needs to be changed to text.
Look below the step where I have changed it to text
Table.TransformColumnTypes(#"Promoted Headers",{{"Branch", type text}, {"FY", type text}, {"Invoice No", type text}})try to delete the step of "Change Type"
8 Replies
try to delete the step of "Change Type"
Thank you so much for your help Daniel. It worked.
Try to clean the data first in those columns as there might be few invisible characters.
Transform menu - Format - Clean
Thanks for the suggestion Vijay. But it didn't work either. Still there's error.
DataFormat.Error: We couldn't convert to Number. Details: 1-H
Can you paste some data in a text file or Excel and share through Onedrive?
