Forum Discussion

masplin's avatar
masplin
Impactful Individual
9 years ago
Solved

export data to excel

I'm struggling to decide whether to move from excel based models to PBI Desktop models. There are number of issues, but a key one is how to export large chunks of underlying model data e.g. customer lists that feed 3rd party CRM, or lists for finance to do reconciliation

In excel this is trivial as you just write some DAX in a SUMMARIZE function and spit out whatever columns/rows you need. As far as I can tell there is no way to do this from a PBIX model

1. Export data IN PBI Desktop only exports csv which then requires additional formatting and manipulation by non-technical marketing staff. non-starter.
2. Making a report and publishing to PBI Services you can export to excel directly, but if you just want to look at an ad hoc list of 100 rows in PBI Desktop thisis an uneccessary extra complexity and you can't make alist that long in Desktop (unlike excel) 
3. Dax Studio can link to the PBIX model, but again can only export in an awful csv structure. No choice to use Excel unless your data model is already within excel

 

Have I got this correct and DAX studio cannot export directly to excel and save a lot of grief? I find it astounding that this basic feature is avaible in PBI Services and not on the desktop!!

 

Thanks for any clarification
Mike

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi masplin,

     

    Thanks for your suggestion. It's true that export to CSV is only available in service rather than Power BI desktop now. For your requirement, you can vote this idea: export excel.

     

    Best Regards,
    Qiuyun Yu

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    If you are in the PBI Desktop, you can go to the Data View and right click on any table name.  On this menu is an option "Copy Table" which attempts to put that table into your clip board.  Larger tables will have a "Please Wait" message.  Once this is done, you can simply paste into your Excel.

     

    I've done this for a few audits.

    • masplin's avatar
      masplin
      Impactful Individual
      9 years ago

      Hi Ross

       

      not quite what is needed. I need ot produce listsof say filters cusotmers, but with detial from related tables or similar corss table lists for finance often hundreds or thousands of rows. In Excel I can either just write a DAX SUMMARIZE function to createa table or do a tabular pivot table. 

       

      While I can create a table in Desktop I can only export as CSV which the fiance/marketing people woudl have to reformat to make use of.  I could publish everything up to Service and export as Excel, but that again is not great for doing ad hoc queries.

       

       Weird that you can't export Desktop directly to excel as you can from Service.  So for this type of output excel seems significanty superior. 

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        9 years ago

        Hi masplin,

         

        Thanks for your suggestion. It's true that export to CSV is only available in service rather than Power BI desktop now. For your requirement, you can vote this idea: export excel.

         

        Best Regards,
        Qiuyun Yu