Data format error when using dataflow to import all files from a sharepoint

[sagdec1212]

Hi all, I got an error "DataFormat.Error: We couldn't convert to Number" when I tried to use dataflow to extract all Excel files from a Sharepoint folder. The query I copied exactly the same from Power BI desktop. I was just trying to move it to the dataflow in my workspace.

At first, all the parameter and my sample file were transformed to tables. And then I unclick the 'Enable load' and tried again, and it shows 'Error: DataFormat.Error: We couldn't convert to Number.' I have no idea why. Could someone please help? Thank you so much!

[Anonymous]

Hi @sagdec1212 

You may need to modify the Power Query used in the dataflow to explicitly convert text to numbers. You can use the function to convert text values to numbers. Here's a simple example:

= Table.TransformColumns(YourTableName, {{"YourColumnName", each Number.FromText(_), type number}})

Best Regards,

Jayleny

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

[SaiTejaTalasila]

Hi @sagdec1212 ,

As per my knowledge the helper queries generated while combining the files causing that issue.Import those files again on dataflow after combining the files you can edit the power query by copy paste the next transformation steps from your desktop file power query to dataflow.

Thanks,

Sai Teja 

[sagdec1212]

Hi Sai, sorry but I am a bit confused. Could you please explain more in detail? What should I do with the helper query (parameter/funcion)? Thank you so much!