cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hambach
Frequent Visitor

FormatNumber error due to Null values?

Hello everyone,

 

I am trying to build a flow, so I can send my monthly PowerBI data as an HTML Table around in an E-Mail.

 

So far it is looking fine. The numbers get imported into a table but the percentage values are still show as ugly decimal:

Inputs.png

before.png

 

So instead I use formatNumber() around the items to convert these values to nice percentages:

 

Problems.png

 

The weird thing now is for these 3 value columns it works, but the other 3 not. I get this error message that the functions is receiving Null values as it's input:

 

Error.png

 

All values are coming rom the same Power-Bi datasource table. My first guess was that maybe there was like 1 Null value in these columns that is crashing the whole function, but even if I substitute the function with a check like :

if(equals(item()?['Actual vs. Plan Revenue'],null),0,item()?['Actual vs. Plan Revenue'])
I will see that he sees everything in the column as Null value and sets it to 0.
 
I am not sure where the Null error is coming from and how there is a difference between my columns, some work, others don't.
Can somebody help me?
 
Thanks and have a great week!
 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Expiscornovus
Super User
Super User

Hi @Hambach,

 

The easiest way to find the internal name would be to add the field without formatting and then hover of the field.

 

tooltip.png

 

Alternatively, you could also use the peek code in the settings of the action to find the actual internal name of the field you want to use for your expression. 

 

internalnamefield_select_peekcode.gif



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


View solution in original post

4 REPLIES 4
Expiscornovus
Super User
Super User

Hi @Hambach,

 

In the example of the Actual vs Plan Revenue column it looks like it is a case of a missing dot in the column name.

 

Have you tried the below expression without the dot behind the vs:

 

 

if(equals(item()?['Actual vs Plan Revenue'],null),0,item()?['Actual vs Plan Revenue'])

 

 

missingdot.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Hi @Expiscornovus 

omg I am dumb, thank you so much!

It really was a forgotten dot!

Do you know what the problem with the PR/Availability YTD could be?

I changed it to a more uniform format for all but it is still giving me the issue:

 

PR YTD_1.pngYTD_2.png

 

Thanks for your quick response!

Expiscornovus
Super User
Super User

Hi @Hambach,

 

The easiest way to find the internal name would be to add the field without formatting and then hover of the field.

 

tooltip.png

 

Alternatively, you could also use the peek code in the settings of the action to find the actual internal name of the field you want to use for your expression. 

 

internalnamefield_select_peekcode.gif



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Hi @Expiscornovus 

 

thank you for your great help!

It was indeed one single row which had an empty value and I fixed it. 

 

Just as a question for my next steps, do you know if my table->HTML->e-mail flow is possible with a matrix instead of a simple table as well? Any big changes I would have to do? Thanks and best regards!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (3,198)