I have a column that contains different data types, dates, numbers, text.
I only filter to the rows that I need which has number which I think the data type is decimal number.
However, when I chose decimal number as data type, it gives me errors.
I speculate that even though I only filter to the rows that I needed, power query are not only converting the data type to my filtered rows but for every values in the column, even though they are filtered out, hence the error.
What is the best solution for this?
thanks
1 Like
Does this work for you?
= Table.AddColumn(#"Changed Type", "IsNumber", each Value.Is(Value.FromText([Notes]), type number))
It will return TRUE or FALSE, then you can filter down to TRUE afterwards. Then change the data type to Decimal Number.
2 Likes
Hello @JhazPetersen
Did the responses above help solve your query?
If not, can you let us know where you’re stuck and what additional assistance you need?
If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION
Thank you
We’ve noticed that no response was received from you on the post above.
Just following up if you still require assistance to solve your inquiry.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
It didn’t work on my end.
I have errors when changing data type, but I decided to remove errors instead
Did you try what I suggested @JhazPetersen? Are you trying to change the data type before filtering out the FALSE values?
1 Like
PQ gave the errors table afterwards after filtering more rows the errors reduced to 1% which I just decided to remove errors. thank you for a swift response
1 Like
if there is text in the field. pq will automatically put it to text. The only way is make two columns one for text and one for numbers.
1 Like
Out of interest, what values were in the cells that were still throwing an error?
1 Like
Hello @JhazPetersen
We’ve noticed that no response was received from you on the post above.
Please provide the information DavieJoe is asking for so he can help you with your inquiry.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Due to inactivity, we’d like to conclude that you no longer require assistance to solve your inquiry.
If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.