Excel Power Query Data Model Append Table

[ToffeeMark]

Hi,

I've taken CSV files for the last 4 years and created an append table in the excel data model with +9 million rows. Can I export the append table I created into a csv from the data model/excel?

Thanks

Mark

 

[baitmaster]

Your obvious issue working via Excel is the limitation on rows. There's a way to do this from Power BI, which uses the "same" Power Query interface that you use in Excel, and which has recently been updated. I don't think the same update has happened in Excel but I'm not on 365 so I might be missing something. Anyway, take a look at

to see a direct export to csv from the Power Query editor, noting that it's in Power BI not Excel

I'm unaware of any row limitations in .csv, but there may be an issue there too. Suggest you move across to a database if you can, given the amount of data you're working with, or find some way to distil it

 

[ToffeeMark]

Thanks Baitmaster, I'll have a look at the power bi route.
I've used Access, Power BI and Excel but never any other database, I think that's the way I could go long term but I'm not sure where to start, there didn't seem to be a platform I could try that is intuitive or has a an easy to understand user interface.
For example the database comes separate from the database management studio? got confusing so didn't pursue it further at the time.

If I really knew what I was doing I'd be loading in monthly 1gb files and then combining them and querying them using power bi or something. Is Azure the way to go? No idea how to get started with that and it sounded costly.
Thanks again.

 

[anvg]

Hi
You could use DAX Studio for export data from Power Pivot model to csv file with executing DAX query if it is needed.
Regards,

 

[anvg]

Or use VBA (if you have 2016 or newer)

    Dim pConn As Object, pRSet As Object
    Set pConn = ThisWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection
    Set pRSet = pConn.Execute("EVALUATE 'YourAppendTable'")
    'store recordset data here
    pRSet.Close()

pRSet will be contain data rows from your table. And you could save data from it by a standard code for storing recordset to csv file. Like that Save Recordset To CSV File
Regards,