Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PabloVR
Frequent Visitor

Dataflows can't convert text column to a number and divide it in the same step (In Desktop it works)

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?

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

Try using 

 

each Number.From(_) / 1.25

 

Pat

Microsoft Employee

View solution in original post

2 REPLIES 2
ppm1
Solution Sage
Solution Sage

Try using 

 

each Number.From(_) / 1.25

 

Pat

Microsoft Employee
PabloVR
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors