Custom Column text values generating error

[Sammynm]

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?

[v-yanjiang-msft]

Hi @Sammynm ,

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 _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sammynm]

Hi @v-yanjiang-msft  

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 

[v-yanjiang-msft]

Hi @Sammynm ,

May I know if the column referenced by your custom column is in the error state? 

Best Regards,
Community Support Team _ kalyj

[Sammynm]

Hi @v-yanjiang-msft , yes the original column is in the same error state. I actually removed the custom column since it did not help resolve the issue. Again, the problem is that the column is not recognizing the text values in the column, which both text and numeric values. And again the column has been assigned as a Text column.

[v-yanjiang-msft]

Hi @Sammynm ,

If the original column has error, the custom column certainly be error.

Now what we say the original column, Is it always in the data source? If you manually enter a table with the error column in Power Query, see if there are any errors.

I don't think it has anything to do with the fact that your column contains several data types. You can simply give an example and try it. The Text type will not go wrong. It should be related to what you have done to this column in Power Query.

Best Regards,
Community Support Team _ kalyj

[Sammynm]

Hi, I resolved it. I had to delete the steps Power BI suggested and then assign the column myself.

[HotChilli]

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

[KT_Bsmart2gethe]

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.

[Sammynm]

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.

[KT_Bsmart2gethe]

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