locked
Can Power BI read data from data model RRS feed

  • Question

  • Hi,

    I use Power Query in Excel (named as ExcelA) to create a complex query (named as TestQ), which includes 3mm rows of data. Now I need to export TestQ to a csv file. Is there some way to do that? Since Excel cannot hold 3mm rows, loading data to Excel first and then saving as a csv is not possbile (or is it?).

    When I was searching for results online, I found this

    http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/

    In the Power BI environment, I can use R (as add in) to export data to CSV or DWH. But from my understanding, I have to use Power BI to reproduce the TestQ in ExcelA first. After that, I can use R (as add in) to export data to CSV. Is there some way to use Power BI to link to the data model (which is created in ExcelA) only, and use R to export data instead of reproducing TestQ in ExcelA?

    Thanks


    Wednesday, January 20, 2016 5:24 PM

Answers

  • From 2016 onwards you can access the PQ data model in Excel via VBA. Then you could write directly from Excel into csv.

    But with 2013 you have to transfer the Power Query part of your ExcelA to Power BI. But you don't have to do that manually: In PBI -> File -> Import -> Excel Workbook Contents.

    If your queries reference content from your Excel-table you will be asked whether this data shall be copied or the connection to that file shall remain. All your queries will be transferred to the pbix automatically. But strangely none of them will be loaded to the PBI datamodel automatically. You will have to enable load on each query, but then you should be fine.

    & Keep in mind, that this is a preliminary feature that could change in the future!


    Imke Feldmann TheBIccountant.com

    • Marked as answer by bjzk Wednesday, January 20, 2016 9:07 PM
    Wednesday, January 20, 2016 6:51 PM

All replies

  • Which Excel-version do you use?

    Imke Feldmann TheBIccountant.com


    Wednesday, January 20, 2016 6:04 PM
  • Excel 2013
    Wednesday, January 20, 2016 6:27 PM
  • From 2016 onwards you can access the PQ data model in Excel via VBA. Then you could write directly from Excel into csv.

    But with 2013 you have to transfer the Power Query part of your ExcelA to Power BI. But you don't have to do that manually: In PBI -> File -> Import -> Excel Workbook Contents.

    If your queries reference content from your Excel-table you will be asked whether this data shall be copied or the connection to that file shall remain. All your queries will be transferred to the pbix automatically. But strangely none of them will be loaded to the PBI datamodel automatically. You will have to enable load on each query, but then you should be fine.

    & Keep in mind, that this is a preliminary feature that could change in the future!


    Imke Feldmann TheBIccountant.com

    • Marked as answer by bjzk Wednesday, January 20, 2016 9:07 PM
    Wednesday, January 20, 2016 6:51 PM
  • Sorry, forgot to mention that you can try your luck with this: http://www.powerpivotblog.nl/export-a-table-or-dax-query-from-power-pivot-to-csv-using-vba/

    You would just have to load your PQ data to Power Pivot.


    Imke Feldmann TheBIccountant.com

    Wednesday, January 20, 2016 7:06 PM
  • Hi Imke,

    Thanks for the manually method. It works, even though it chokes my laptop. But the website you provided could not work very well. It seems like it cannot handle 3mm rows.

    Wednesday, January 20, 2016 9:08 PM
  • Hi bjzk,

    sorry for not pointing out the current 150k row limit on this method. Didn't recon that myself before you were posting about it... :-(


    Imke Feldmann TheBIccountant.com

    Friday, January 22, 2016 8:46 AM