Power Query: Always getting DataFormat.Error We co...

adamjclee · ‎03-20-2023

I'm new to using Power Query and I seem to always get error:DataFormat.Error We couldn't convert to Number. This seems to happen when it really shouldn't so I'd be grateful for an explanation. For example:

I have the following data:

As can be seen, dep_delay is a column of numbers and carrier is a text column. If I try to Pivot on carrier, with dep_delay as value and aggregation function Average, I get a DataFormat.Error We couldn't convert to Number. This is non-sensical to me - everything that needs to be is already of type number. I get the same error if I try to close and load the query.

Worth noting is that, if I simply import the Table from csv using get and transform - but not the PQ editor - the data loads correctly and I can do this pivot etc. in Excel with absolutely no issues - suggesting something strange is going on in PQ. (Perhaps worth mentioning is that I'm using PQ on a mac).

Grateful for any suggestions as to why this might be occuring.

rajulshah · ‎03-20-2023

@adamjclee

Can you please provide the screenshot of the error?

adamjclee · ‎03-20-2023

Sure. First here is the pivot options box.

When I click on OK, I get the following:

rajulshah · ‎03-23-2023

@adamjclee

We are taking average of the values. But since it has "true" value it cannot take the average.

You can take min or max.

Please let me know if this didn't work.

adamjclee · ‎03-23-2023

I'm afraid I don't follow. There is no Boolean (TRUE/FALSE) column, and if something is being converted into Boolean, then something else is going wrong earlier on. The "value column", which the aggregation function ought to be applied to is a column of (decimal) numbers. So it certainly should be able to take the average.

On a related point, if I understand your comment correctly, you are saying that I cannot average a Boolean column to find the proportion of true values? Or, more precisely, Booleans are not cast to numbers [true = 1; false = 0] when applying numerical operations, unless one explicitly changes the type?