Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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}})
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?
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |