cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paulx99 Helper IV
Helper IV

Re: Exporting data from Power BI to Excel

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 !

Super User III
Super User III

Re: Exporting data from Power BI to Excel

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
    TREND

 

Big 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…

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 
Super User III
Super User III

Re: Exporting data from Power BI to Excel

... just saw that our answers overlapped - anyway:

 

Anyone reading this and having coded some statistical functions already that she/he wants to share - please contact me 🙂

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 
Paulx99 Helper IV
Helper IV

Re: Exporting data from Power BI to Excel

No worries, Imke. Thanks as always for your valuable insights. A workaround (in the absence of the required functionality in PBI Desktop) is better than no solution at all !  Paul

pescobar
Frequent Visitor

Re: Exporting data from Power BI to Excel

Hi

 

I have a dashboard in Power BI Service and i'm trying to publish to web and then export to CSV.
But there is no option to export to CSV from the Dashboards.

Could you tell me please if there are some way to do that.
Thanks in advance.

 

Pablo

Paulx99 Helper IV
Helper IV

Re: Exporting data from Power BI to Excel

Hi Pablo

 

Sorry for the delay in getting back to you. Firstly, most of my experience has been with PBI Desktop, not PBI Service. In addition, I have only been a member of this community for a relatively short period of time. So, I am by no means an expert. 

 

I dialed into my PBI Service account to have a look at what you have mentioned and agree there appears to be no option to export to CSV from the dashboards. The only place I can see you can do that is in the reports section. 

 

In the absense of any other members out there having a suggestion for you on that, it might be a good enhancement request to make to make on the Power BI Ideas site.

 

Paul

nbelyaev
Regular Visitor

Re: Exporting data from Power BI to Excel

Hi Guys, 

 

I have just seen this thread, but this workaround might help anyone in the future: 

if you want to export to csv from BI Desktop including all your calculated columns. Put a table visual, add all your fields. Press ... and export.

 

Hope this helps.

 

Natalia  

pvchandu
New Member

Re: Exporting data from Power BI to Excel

    • Currently we have the export to csv,excel using the UI.
      Is there is an api to do it programmatically. Please let me know.
pvchandu
New Member

Re: Exporting data from Power BI to Excel

Currently we have the export to csv or excel using the UI. If there is an api to do it programmatically, please let me know.

Microsoft dkay84_PowerBI
Microsoft

Re: Exporting data from Power BI to Excel

No, there is no way to do this.  The Power BI REST API lets you push new data to a data set, or pull metadata, but not the raw underlying data.  The export option (under the "..." menu for a visual) will export the data as the visual displays it, so if you visual aggregates the data in some way, the export will similarly be aggregated.  Lastly, analyze in excel allows you to connect to the data model in Power BI service, but you are working with a power pivot data model and not the underlying data, and you need to explicitly create measures in Power BI in order to drop them into your values section of the pivot table. 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors