Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I was helping a client with an error in one of his Dataflows and looks like I've found an error in the Service.
We were trying to optimize a query to a Dynamics Business Central Odata endpoint and applied table filtering before expanding the web service that would bring us the information from all the companies. When you expand a filtered table instead of one of the predefined ones that the endpoint gives you, the downside is that all the columns returned are of Text type (No big deal)
The problem is that while Power BI Desktop has no trouble transforming a certain column, the same operation errors out in the service when put on a Dataflow.
let
...
#"Custom added" = Table.AddColumn(#"Filtered rows", "Filtered table", each Table.SelectRows([Endpoint], each [Status] = "this" or [Status] = "that")),
#"Removed Columns" = Table.RemoveColumns(#"Custom added",{"Endpoint"}),
#"Endpoint expanded" = Table.ExpandTableColumn(#"Removed Columns", "Filtered table", {"Field1","Field2","Field3","Field4"}),
#"Divided column" = Table.TransformColumns(#"Endpoint expanded", {{"Field3", each _ / 1.25, type number}}),
...
in
#"Last step"
The "Divided column" step works perfectly in Desktop although the column Field3 is a text.
In the service, a Dataflow running the same steps gives back this error:
Expression.Error: We cannot apply operator / to types Text and Number. Operator = / Left = 0 Right = 1.25
If you convert Field3 to a Number in the previous step, the error disappears.
Is this a limitation of Power query in the Service?
Solved! Go to Solution.
Try using
each Number.From(_) / 1.25
Pat
Thanks Pat that alternative works, now I see that bringing the Power Query desktop M code straight to the service is not such a great idea.
In Power Query Online the standard math transformations are greyed out for a text column, but they're available in the Desktop.
Best regards