Forum Discussion
Custom Column text values generating error
Good day, I created a custom column for decimals and text. In the Custom Column window I saw the message that there were no syntax errors but when I clicked ok and the column was entered into the table, errors loaded in place of the text entries. What should i do here?
10 Replies
Hi,
My understanding from your explanation above, you created a custom column which value contains "decimals & text". Number and Text are two different type. If you want to return a text column, and you will will need "Number.FromText([decimals]). This function will turn the decimals into text. I hope I have answered your question. if not, please share a screenshot or example of the error and what are your desire outcome.
Hi thanks for the response. The image below is the output i want in PBI. Some values are decimals/prices and some are text in this same column.
This is the error that appears in PBI desktop/Power Query screen. The first column was the original load. It was assigned as a decimal column. I think created the second column as a custom column thinking that it would recognize the text and the numbers but the same text values error out. I hope this helps explain better.
Hi,
With the minimum context. I would suggest define the column as a text column. Number can be read as a text but text cannot be read as number. If your the number for calculation, you can create two custom column. One is refer to the column where you define text where the number column you will need Number. FromText(), which will convert the number from text to number.
I hope it helps.
Regards
Kerwin
The custom column is having to deal with data that already has errors. It won't fix them. You need to look at the Power Query steps before this.
Let's assume that the error is because there are non-numeric values in a column which has decimal data type (You can confirm this by clicking in a cell which has an error - but not on the error text itself).
Anyway, back to the problem. Somewhere in the Previous steps, the column will be assigned a decimal type.
You need to edit the step which does that. It will probably be called #"Changed Type" or similar. You can either delete the small section of code which makes the column a decimal or alter it to be text type
Hi Anonymous ,
According to your description, I reproduce your problem. In the add Custom Column dialog, it shows no syntax errors, just means that the formula is grammatically correct, does not mean that the correct result can be obtained.
Click on the yellow Error word, you can see the error detail, in my sample it is data format error. If the column referenced in the custom column itself has an error, then the custom column must also have an error.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for drawing my attention to the error message. Yes I does state that it couldn't convert the text to a number. But even when I click Change Type and select Text as HotChilli suggested it still gives me the same error message for the same text values. see below
Hi Anonymous ,
May I know if the column referenced by your custom column is in the error state?
Best Regards,
Community Support Team _ kalyj
