cancel
Showing results for 
Search instead for 
Did you mean: 

Dataflow Power Query data type not applied properly

I have created a column that previously was defined as TExt.  But I wanted to switch it to Number once I had resolved the data type conflicts.

The problem is that the column header says its number format but the contents is displayed as Text and the next steps is trying to filter numbers and it fails complaining that it can't apply '>' operator to type number and text operands.

 

Here's the instruction that is producing the new column:

Table.AddColumn(#"Trimmed text", "CldStartYear", each Text.Start([DLL1TX], 4), Int64.Type)

And here's the result which in this case, the column on the left is not showing up content as numeric even though the header says it is :

FireFighter1017_0-1634931051156.png

And when I try this:

Table.SelectRows(addcol_CldEndYear, each [CldStartYear] >= 2021)

I got this error:

FireFighter1017_1-1634931422185.png

 

 

 

Status: Investigating
Comments
v-robertq-msft
Community Support

Hi,

Would you like to try to just use the “Change Type” and “Filter rows” functions in the Power query to achieve your requirement simply?

 

Best Regards,

Community Support Team _Robert Qin

v-robertq-msft
Community Support
Status changed to: Investigating
 
FireFighter1017
Helper II

It seems that sometimes it lags so much that it takes a while before the actual type casting is applied.

I will try what you said.  But I would rather have it in a single step.

 

After I posted this issue, I tried to reproduce the issue and it was working fine.

Seems to happen more often when I've been working all day in a model and I haven't saved changes.

 

I noticed Desktop is also struggling when I'm making too many modifications in Power Query between applying changes.  Ended up eating all my 32Gb of RAM last week.  I applied the changes, saved and closed the file.  After I open it again, the RAM usage was more around 15Gb.  Could that be the same issue I'm facing on Dataflows in PBI service?

 

 

v-robertq-msft
Community Support

Hi,

I think this is a normal phenomenon in the case of a large amount of data.

 

You can refer to this link:

https://docs.microsoft.com/en-us/power-query/best-practices

 

Best Regards,

Community Support Team _Robert Qin

FireFighter1017
Helper II

@v-robertq-msft ,

Is 10 000 lines too much?