Forum Discussion
Exporting data from Power BI to Excel
Pivot Tables in Excel are in Compact Form and have Collapse/Expand buttons.
You can summarize the fields and fit a lot of data on one Page! You can Expand only the fields you want to see the details for
With the Matrix in PBI you can't - you have to scroll and scroll and keep scrolling... to get to what you want to see...
Slicers remove data AND field Totals in the Matrix so they do not help
Agree.
So current state seems to be:
If you don't want to drag your data through the cloud but need to perform operations that need a Solver, don't use Power BI but Excel instead.
Best current workaround I know of is to export your data to txt using DAX Studio (unlimited number of rows & you can write your own DAX-statements if you want to export measures as well). Then re-import it into Excel via Power Query (you can even copy your formatting-statement from PBI to be applied there as well) & table output that feeds into Solver.
You could also use the txt instead to feed an R-solver (this blogpost describes how to execute an R-script within a PBI-query) whose results will be played back into Power BI for display.
While no data that I work with from a DA perspective would ever be dragged through the cloud, items of interest identified within that data in PBI Desktop would definately (for my purposes) need to be exported to Excel for end user interpretation, distribution and feedback - not further analysis.
It's a good workaround you've given to export the data to txt using DAX Studio and then re-import it into Excel via Power Query but it would be a lot easier if MS just provided an 'Export to Excel' option in PBI Desktop !
Hi Paul,
sorry, completely misread this.
But not matter of the reasons, my answer was meant as an acknowledge that this feature is missing for some specific purposes.
However, there are workarounds at the moment (if DAX-studio doesn't work for you, you can use the R-feature within PBI as an alternative to export tables with a max. length of 100k rows).
Re descriptive statistics, I see no reason at all, to switch to a different tool. (Apart from the time you need to) Write your functions in M. Currently writing a blogpost about it, but here is a sneak-preview to give you a taste (Excel's TREND-function):
(YList as list, NoOfIntervalls as number) => let Source = Table.FromColumns({YList}), xAxis = Table.AddIndexColumn(Source, "Index", 1, 1), Rename1 = Table.RenameColumns(xAxis,{{"Column1", "y"}, {"Index", "x"}}), AvgX = List.Average(Rename1[x]), AvgY = List.Average(Rename1[y]), x = Table.AddColumn(Rename1, "xX", each [x]-List.Average(Rename1[x])), y = Table.AddColumn(x, "yY", each [y]-List.Average(x[y])), xy = Table.AddColumn(y, "xy", each [xX]*[yY]), xXx = Table.AddColumn(xy, "xXx", each [xX]*[xX]), a = List.Sum(xXx[xy])/List.Sum(xXx[xXx]), b = AvgY-(a*AvgX), ListIntervalls = {List.Max(Rename1[x])+1..List.Max(Rename1[x])+NoOfIntervalls}, TableIntervalls = Table.FromList(ListIntervalls, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Rename = Table.RenameColumns(TableIntervalls,{{"Column1", "x"}}), Values = Table.AddColumn(Rename, "y", each [x]*a+b), TREND = Table.Combine({Rename1,Values}) in TRENDBig advantage of M is that you can do recursive operations as well, there are LOG-functions – so a pretty good basis to get going with descriptive statistics in Power BI.
Should find a space where we could share these codes within the community as well…
