Data Import does not add to data model

[DelEsc]

Hi,

I'm hoping this is something simple. I'm not great with VBA and still feeling my way around other people code snippets and making things work for me.

I have a macro which connects and queries multiple external tables into a pivot table in my spreadsheet.
This works fine.
The issue is that I need to import that data in to the data model in order to be able to use a specific option in the pivot table (summarise the value using "Distinct Count")

This option is not available unless the data is in the data model.

Any help appreciated!

[CK76]

Instead of adding query directly to PivotCache. Use Workbook.Connections.Add2 method to load to data model/powerpivot.

See link for details.
https://msdn.microsoft.com/en-us/lib...ions.add2.aspx

[DelEsc]

Thanks CK76,

Yes, I had seen that option but I have know idea really how to modify the code.
I found most of it elsewhere and made a few basic changes.

I can spend more time trying to figure it out.
Main issue I think for me will be how to pass the array values (multiple files) to the connection string.

Thanks!

[CK76]

Just leverage PowerQuery (Get and transform).

You can use macro recorder to record your process of putting connection into data model. This will give you base code.

As for multiple files... are they all in same structure and being merged into single data model. Or are they different tables that are related via key column?

[DelEsc]

The files are mostly the same but occasionally a different number of columns.
So I query for specific column header names and just import those (from one sheet in each file)

I'll keep at it. I usually get there in the end. Thanks again.

[CK76]

Refer to the link for how to merge multiple file using PowerQuery. See post#6
https://www.excelforum.com/excel-gen...workbooks.html

Note that you'll need to modify steps a bit, as you have to choose columns.

[DelEsc]

Thanks, I'll take a look.

[DelEsc]

Just to close this out.

I found out that a simple 'double click' on the "Grand Total" value of my existing pivot, recreated all the data from that pivot table in a new table in a new sheet.

All I did then was macro record myself doing this(.ShowDetail=True), renaming the new sheet, deleting the old Pivot table and recreating a new pivot from that new table data (Choosing "add data to the Data Model')

Certainly not the most efficient way to do this but I got what I needed.

Thanks.