Whole number not going into Values field

[Yahya]

Dear community,

I've become crazy with this problem as I don't find any reason why Power BI wouldn't recognize the column I want to use as a whole number.

Calculation is simple, I have in Power Query 2 columns with dates from which I make a new custom column for the days difference

   #"Inserted Date Subtraction" = Table.AddColumn(#"Filtered Rows2", "Days difference", each Duration.Days([Feedback Pxm] - [Requested date]), Int64.Type),

 When clicking done, it's immediately recognized as a 'whole number'.

When importing to Power BI, I see immediately something wrong, the Σ next to the column is missing.

After verifying the column tools, the data type is Whole number, Format is Whole Number, Summarization is Sum. But it's impossible to put the column into the values field.

I've made sure there's no null or blanks or empty cells are on the power query side by filtering it out by force and using the 'remove empty'.

I've even force changed type to decimal value and back to whole number to make sure there's a separate conversion step. still nothing

I'm out of ideas of why this simple column which contains only numbers can't be put in values...

Am I missing something?

[kmsmith1108]

Was this problem ever solved?   I have a field that is a whole number in PowerBI, but when I use the data set outside of PowerBI, as in using the data set in Excel, I am not able to use the whole number field in the Values of a Pivot Table.  I created a measure and I am able to work around the problem, but why should I?

@Yahya  @amitchandak 

[Yahya]

I was never able to fix the issue. What I did is do the pbi file again and do everything anew.

As for Excel, it doesn't support the number fields when you import from power bi dataset (silly isn't?). The only way is to prepare your measures and publish them, then refresh excel.

Not very practical since Excel would perfectly be able to do this if only it wasn't recognized as "text"

[kmsmith1108]

Thank you for your response.   You're correct that this is silly.   If I create a data set in PowerBI as part of a report or dashboard that I want to make available for use across the company in the DataVerse or via Analyze with Excel, there should be no  limitations that I have to explain to users.   They want seemless integration and this is not entirely seemless. 

[Yahya]

ok, I'll log an issue. Thank you Amit

[amitchandak]

@Yahya , would it possible to share a sample pbix after removing sensitive data.

[Yahya]

I'd love to, but it's an SQL connection through IMPORT. and strangely, when opening on a computer outside my organisation, you can't get to the downloaded data.

When I do an Excel copy and import it to a new power bi, the Σ appears.

[amitchandak]

@Yahya , I have seen a few places Σ did not appear, but still worked in dec 2020 release. Not working with whole-number data type with summarization is strange

[Yahya]

It's a real pain, yes.

The workaround I use currently is simple DAX like sum(Days Difference) but it's very strange indeed...

[amitchandak]

@Yahya , In any case, record a small video and log an issue. or check if someone else reported that https://community.powerbi.com/t5/Issues/idb-p/Issues