cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Manikanadan-R
New Member

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.

2 ACCEPTED SOLUTIONS

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

View solution in original post

wdx223_Daniel
Super User
Super User

try to delete the step of "Change Type"

View solution in original post

8 REPLIES 8
wdx223_Daniel
Super User
Super User

try to delete the step of "Change Type"

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

Vijay_A_Verma
Super User
Super User

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? 

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)

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

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!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors