Append Data to Data Model

[MattH2]

[FONT=&quot]Hi, I need some help.

I have invoices from customers listed out in separate worksheets, one for each month in 2017, 12 total. About 200k rows of invoices in each sheet. So, I imported each worksheet into the data model of a new workbook (using Power Query, Get and Transform). But I'm having trouble Appending the tables together. They are lined up exactly the same, each column has the same name and in the same location.

I can't seem to append February's invoices, for example, to the end of January's, and so forth to make one big list of invoices for the full year 2017. I can't copy and paste because it will add up to over 2 million rows. I need them all in one data model table so I can do a big PowerPivot on the whole thing.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]I tried the Get Data dropdown -> Combine Queries -> Append selection and I did "3 or more tables" and did all 12 at once and it gives me an error after processing for 15 seconds.

Thank you for your help.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot] [/FONT]

 

[Fazza]

Maybe put all the data in an mdb file/table.

If leaving it in Excel the data can be sourced from the 12 worksheets using a UNION query.

Assuming same fields on each worksheets, and worksheet names "Jan", "Feb", Mar", etc You could use

SELECT * FROM [Jan$]
UNION ALL SELECT * FROM [Feb$]
UNION ALL SELECT * FROM [Mar$]
etc

And if you wanted the month name added as a new field

SELECT 'Jan' AS [SourceMonth], * FROM [Jan$]
UNION ALL SELECT 'Feb', * FROM [Feb$]
UNION ALL SELECT 'Mar', * FROM [Mar$]
etc

You mention transform, if you need that too you could do via something like

TRANSFORM SUM(field)
SELECT row field/s
FROM (repeat SQL from above)
GROUP BY row field/s
PIVOT column field/s

With 2.4 million records it will take a little time but you should see something in the status bar showing there is processing underway.

 

[MattH2]

Thanks so much. yes from a databse point of view and Access/SQL programming this would work. Good point. I'm a little nervous to try with the language because I always happen to create bugs somehow.

I was showed a way from a friend, if I name the tab of each month's sheet the same name, like "invoices" then I could Get Data from a Folder instead of a file and import them all at the same time into one pivot table. I'm still working on it now, but I sampled it with the first 3 months and it worked perfectly.

Thank you so much for your help.

 

[Fazza]

What I described can be done totally within Excel for pivot tables (such as a single pivot table having data from all 12 worksheets) or a query table. I wasn't expecting work outside Excel - just mentioned that option due to the data quantity. Sounds like you've found a solution one way or another.

 

[MattH2]

Thank you so much, yes. I'm glad to have found a solution. And I really appreciate your solution, because I would have used what you mentioned for sure if I didn't have another way to do. I'm a little nervous with programming, even as simple as it is, because I'm not good at diagnosing bugs (atleast yet!)

Thank you!!