Error null cannot converted to text (but there is no null value in column)

[FlorianF]

Hi guys,

I have a strange error in Power BI.

It says that a null value cannot be converted into text. But the column where the error disappears is not a datatype text, and also there is no null value.

Also I tried to replace the values from null to an empty string, but that does not fix the error.

How can I fix that?

[FlorianF]

I added the function with the menu and selected the values here.

I do not know, how and where to implement the if statement.

Can anybody give me an example?

[BA_Pete]

For your scenario, i.e. running the function as a source, you could try:

(Customer as number) =>
let
    Quelle =
    try
        Json.Document(
            Web.Contents(
                "https://ffbc.weclapp.com/webapp/api/v1/archivedEmail?entityId="
                & Number.ToText(Customer)
                & "&entityName=party&pageSize=1000"
            )
        )
    otherwise null
in
    Quelle

Pete

[FlorianF]

Sorry I was wrong.

The number always has a value. The error come up, when the result of the function is empty. So I have to check if the result is empty and go to the next and write empty string in the columns of the query I use the function.

I think at this point, where the function will executed I have to check if the result is empty and continue with the next number.

[BA_Pete · Accepted solution]

Ok, update the highlighted section to be:

each try f_archivedEmails([id]) otherwise null

Pete

[FlorianF]

Thanks! I found out what the reason for the error was.

But to fix it, I need to update my function that it only runs the query when the number is not null.

How can I achieve this? Tried it with if statement, but I'm pretty new and do not know how to correctly add an if statement here.

(Customer as number) =>

let

Quelle = Json.Document(Web.Contents("https://ffbc.weclapp.com/webapp/api/v1/archivedEmail?entityId=" & Number.ToText(Customer) & "&entityName=party&pageSize=1000"))
in
Quelle

[BA_Pete]

I think you would apply the conditional at the point of calling the function, rather than changing the function iteself, something like:

if [Customer] <> null then myFunction([Customer]) else null

Pete

[BA_Pete]

Hi @FlorianF ,

I don't think you'll see a null in your filter list in this scenario as the cell won't be null, it will be an error value.

Try selecting your [company] column then go to the Home tab > Keep Rows > Keep Errors.

Hopefully this will leave only the rows with errors which you can review/troubleshoot as required.

Pete