Currency type between Power Query and Power Pivot

[csotstot]

I'm running into something really painful using Office 365 (using O365 2016 versions of the local installs). I'm running into an issue that reading seems to indicate was resolved in 2013. If I set a column to a currency data type in Power Query and use the column in Power Pivot, Power Pivot changes it to a decimal number data type. I have a pivot table that needs a currency data type for a row, so values don't show as desired (e.g. $0.00 just shows as 0). I'm just wondering if anyone knows whether this is still an open issue in spite of what I've dug up in research? In the interim, I'll just format the rows in the pivot table...

Thanks!

 

[ParamRay]

Hello, have you tried setting the column's data type to currency within Power Pivot? (On the Home tab, in the Formatting group.)

 

[csotstot]

Hi ParamRay,
Unfortunately, trying to change to a currency type in Power Pivot doesn't work. It presents an error that the type was set in Power Query and needs to be changed in Power Query. I can change the format in the data model management window, but since the type is still decimal, it shows incorrectly in the pivot table. From everything I can see, there is still an issue with currency types not flowing through properly from Power Query to the data model for Power Pivot; I'm not sure if there is an issue with my installation or if others are still seeing this...

 

[MarcelBeug]

FYI In my version (O365 Excel 2016 Version 1707, Build 8326.2052) it works correctly: currency fields in Power Query show up as currency in the data model / Power Pivot.
Did you try with a really simple table just to verify if it has nothing to do with any specifics in your query/-ies?

 

[csotstot]

Thank you Marcel!
That may be part of my issue. We're on a deferred channel build and our revision is lower. I'll double-check to see when we might be getting up to this revision...
Have a good morning!

 

[csotstot]

FYI...A table created directly within my install seems to work in passing the data type (It shows currency as the data type in the data model), although it appears that currency is not displayed as such if the currency data is part of a pivot table row. Interesting... I'll definitely need to look at the source workbooks; I'm still not sure if the row behavior is what I should be expecting...