Automate Flow failing - column contains both numeric & text

[unknown917]

I have a column in PBI that Flow is classifying as type: number.  The column contains percentages and '-' when no percentage is calculated.  My trigger is failing.

Am I correct in assuming it is failing because Automate cannot read the '-' as a number?  

Is there a way to change the type in the execution of the flow? 

[v-jtian-msft]

Hello,rajendraongole1 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@unknown917 .I am glad to help you.
I agree with rajendraongole1's suggestion.
1.Preprocess data containing ‘-’ in the power query by replacing it with another character or null.
use Text.Replace M function.
Text.Replace - PowerQuery M | Microsoft Learn
It's also a good idea to use DAX code to make IF judgements on the data.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[rajendraongole1 · Accepted solution]

Hi @unknown917 - Yes, you're correct in suspecting that the '-' values in your percentage column are causing the issue. In Microsoft Power Automate expects consistent data types, so encountering a '-' in a column classified as a number can lead to errors during execution.

Solution 1: Handle the '-' Value in Power BI Before Sending to Power Automate
In Power BI, you can create a new calculated column or use the existing column to transform the values

create a calculated column as below:

Cleaned_Percentage =
IF(Table[Percentage] = "-", BLANK(), Table[Percentage])

This will replace '-' with a blank value or substitute with 0 also fine.

It works, and you’ll avoid flow execution failures by ensuring all values Power Automate receives are numeric.

[unknown917]

thank you!