Analyze in Excel open wrong Excel version

[masplin]

I have both excel 2010 and excle 2016 installed as have avery old model I need to refer to.  i have set all the file types od* to Excel 2016 in default programs, but when I do analyze from Power Bi Service it only opens in excel 2010.  Is there any way to make it use Excel 2016?

Thnaks

Mike

[GilbertQ · Accepted solution]

Hi @masplin

What I would suggest, is to rather download and install the Power BI Publisher for Excel. This is an Add-In that you can install for Excel 2016. And when you have it installed you can then easily connect to your data directly from Excel without having to download the ODC file each time.

Here is the link: https://powerbi.microsoft.com/en-us/excel-dashboard-publisher/

I personally use this option, easier and quicker.

As well as this should then solve your Excel opening in the wrong version issue.

[masplin]

Hi

Out of interest i used your method and connected excle to my dataset.  I created a small pivot table and double clciked a cell to force the creeatio of a table (the old excel 2010 triick.  I then edited that query as below

I thougt this might work but get the error

"cube either does not exist or has not been processed"

Id that what you would expect?  Just stuck trying to get all my modles inot Desktop and preserve the functionality I need

Thanks

Mike

[GilbertQ]

Hi @masplin

I did test what you are looking to do in Excel and I got the following below, which looks exactly like a table, but it is coming directly from the dataset in the Power BI Service. As you can see below it does have the cube formula's

[masplin]

so connect to the dataset, but then add cube formulae in a fresh sheet?  I've never used cube formula so wil lgive that a try.

Someone else asked a siilar question nand seems you can use analyze in excel and then use the old pivot table double click and then paste in the dax code as i used to.  i need ot try that out as well

Desktop/How-to-export-some-huge-query-results-in-excel/m-p/201684#M88752 

[masplin]

Perfect.

Is it possible to use a sumarize statment to output a whole load of underlying data inot excel using this? i have to churn out blended tables of cusomter and product data for the marketing department.  In excel I just created a table and added a DAX evalautate sumarise statement to get the columns I wanted. 

Mike

[GilbertQ]

Hi @masplin

Yes this can be done, you can do it once you have connected to the Power BI Service and then convert them to Formula's

And that will then mean that you can then use it to Summarize or put the data where you require on your Excel sheet.

Here is a great example below on how to convert to the Formula's and then use them.

https://dataonwheels.wordpress.com/2015/01/27/excel-bi-tip-18-using-cube-functions-to-break-out-of-p...

[masplin]

That's a great link.

I'm not sure i understand how I get a list currently generate by this sort of DAX statement? Can I create a table once linked up and enter this code?

evaluate calculatetable( summarize( vehicles, vehicles[VRM],customer[mobile phone no],customer[e-mail],vehicles[mot due date],vehicles[make],vehicles[model],vehicles[date of 1st registration],customer[last centre],vehicles[next service date], vehicles[last visit date], vehicles[service due],vehicles[customer 1y active], customer[full name], customer[address],customer[address 2],customer[city],customer[post code],customer[customer group],customer[Plat MOT Avail], customer[Plat XS Avail],customer [MOT reminder platinum], customer[XS reminder platinum],customer[salutation],customer[first name],customer[surname] ), FILTER( vehicles, vehicles[mot rem output]="yes" ) )

ORDER BY vehicles[MOT Due Date], vehicles[VRM]

[GilbertQ]

Hi @masplin

If you are looking to create a table using DAX you would then have to do it in Power BI Desktop based off your original data. I am not sure that you can create a table that you are looking for using the connect using Excel.

[masplin]

That's what i thought which is a shame.

Thanks anyway