How to replace values in a column in power query

[Manikanadan-R]

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.

[Vijay_A_Verma · Accepted solution]

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}})

[wdx223_Daniel · Accepted solution]

try to delete the step of "Change Type"

[Manikanadan-R]

Thank you so much for your help Daniel. It worked.

[Vijay_A_Verma]

Try to clean the data first in those columns as there might be few invisible characters.

Transform menu - Format - Clean

[Manikanadan-R]

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

[Vijay_A_Verma]

Can you paste some data in a text file or Excel and share through Onedrive? 

[Manikanadan-R]

https://1drv.ms/u/s!AuTuEhWYL9eEjyWPJ1quqFaxW1at?e=tPiIaz

To make it simple I have just selected first 3 columns from my original data.

There are 2 workbooks.

1- Raw Data (The file where I save all my day to day activity)

2- Power Query file (The file where I want the selected columns to be updated automatically as I keep updating my raw data)

[Vijay_A_Verma · Accepted solution]

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}})

[Manikanadan-R]

Thanks for your time and effort suporting me. Appreciate that. Your answer gave me a spark. Eventhough it didn't work in my case, I checked where it went wrong and found that the "Change Type" step was the issue. I removed it and cleaned the column as you suggested. It worked. Later I saw the same solution posted by Mr. Daniel in this post. Thanks!! Have a good day!