Sumerize text data fields in Excel (Pivot Table) from a PowerBI Data Set.

[Anonymous]

Hi all,

I am having an issue summerising a text or date field in Excel where the data is coming from the Power BI data set in the service.

1. I have connected to a Power BI data set with my excel sheet.
2. Then I created a pivot table
3. Next I wanted to add the latest / max data as value in the Pivot Table but Excel won't let me. Any time I try to move the date field in to the Values box Excel complained with an error sound. In addition all the field setting are greyed out.

Now I know this can be done if the data comes from an excel table. I can also do the same action in Power BI. I have yet to see if it is an issue when connecting to Analysis Services.

I came up with one work around where I created a measure in the data set inside of Power BI that returned the max date.

But that is not very efficient as I would have to create a measure for any text or date field the end user wished to summerise.

Another I idea I had was to bring in the data to Excel as a regular table (not a Pivot Table). Then the end user can create what ever they want from the basic table.  Only issue is I can't seem to figure out how to do this.

Any ideas or solutions would be welcome

[Anonymous]

Thank you for this, now that I knew what to look for. I found this issue raised before, for those who may be interested: https://community.powerbi.com/t5/Desktop/Analyze-in-Excel-aggregation-of-numeric-fields/m-p/25284.

[v-deddai1-msft]

Hi @Anonymous ，

You can create a calculated table by summarize groupby or addcolumns function in power bi . Then use the excel to connect to the power bi dataset.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon, thank you for responding. So I take it you are saying that this is an genuine issue/bug. I am guessing it is because Excel's Power Pivot is not geting the data types properly from the service or something to that effect.

I appreciate the idea of using a calculated table.. But I don't see this as a practical alternative. This would mean I would have to ad a calculated table for every table just so that I can get normal funcationality.

In short, I interpret this to indicate for the moment that there is no solution and I need to wait for it to get fixed (assuming it can be) and raise a ticket for it. In the time being create measures as needed but if there is too many then simply do not use the PBI dataset as a data source. Instead use a direct to source (e.g. database/API) connection within Excel.

Other options to explore would be to see if the issue exists with analysis services, but I suspect it does.

[ebeery · Accepted solution]

@Anonymous By design, the "Analyze in Excel" feature for analyzing Power BI datasets in an Excel pivot table does not support drag-and-drop aggregation of numeric fields.  Your dataset in Power BI must have pre-defined measures.  See the documentation below.

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel#requirements