Search code examples
excelvbapowerqueryunpivot

Unpivot file with power query results in file with more than 1 million records

Is there any chance to get a file splitted before the unpivoting process is completed? I have tried to do the opposite, that is to split the file and then unpivot it, but it requires too much time due to the fact I have to deal with more than 20 files and I can't be able to write a functioning Macro to do the job. I have to materialise it because I have to load the files into another software that does not have the unpivoting function in it and I can't get direct access to their databases to run any queries.

Solution

  • There is a possible solution using VBA. In this approach you need to:

    1. Load the Power Query table into Power Pivot Workbook Data Model
    2. Fetch data from the Workbook Data Model using VBA
    3. Write the records into a CSV file

    Below is step by step how to.

    For proofing, I created a dummy table of 3 million rows inside Power Query using below code, naming it "MyData".

    // MyData: A table of 3 million rows

let RowCount = 3000000
in Table.FirstN(
    Table.FromColumns({
        List.Generate(() => 1, each true, each _ + 1),
        List.Generate(() => Number.Random() * 1000, each true, each Number.Random() * 1000)
    }, type table [ID = Int64.Type, RandomNumber = number]),
    RowCount
)

    MyData

    I loaded this table into the Workbook Data Model by selecting Close & Load To, then selecting Only Create Connection and checking Add this data to the Data Model.

    Then, using below VBA code, I was able to connect to the Data Model, fetch the records, and dump it to a CSV file.

    Const OutFile As String = "C:/path/to/MyData.csv"

Dim Wb As Workbook
Set Wb = Application.ActiveWorkbook

Wb.Model.Initialize

'Connection to the Data Model
Dim Conn As Object 'ADODB.Connection
Set Conn = Wb.Model.DataModelConnection.ModelConnection.ADOConnection

Dim Recordset As Object 'ADODB.Recordset
Set Recordset = CreateObject("ADODB.Recordset")

Dim Query As String
Query = "EVALUATE MyData" 'DAX query to return the entire table

Recordset.Open Query, Conn

Dim FileNum: FileNum = FreeFile()
Open OutFile For Output As #FileNum

Do While Not Recordset.EOF
    Write #FileNum, Recordset("MyData[ID]"), Recordset("MyData[RandomNumber]")
    Recordset.MoveNext
Loop

Close #FileNum