Excel 2016 - Power Query - Data Model Loading issues/explanation

[Ohhaa]

Hi Folks,

Not too sure if this is a bug or something but I realize that not all my DataModels load to a New Query in a New Workbook.

This is what I'm doing.

1) I created data models within each workbook (there a total of 5 workbooks). I load all as data models as connections
2) Created a NewWorkSheet to host all the other 5 workbooks

Somehow I can't find some of the data models when I'm connecting via ' New Query -> Workbook".

Here is what I noticed:
1) Some of the data models can be detected and load onto the New Workbook
2) As for those that can't be detected, I've to load as a table. And when I try to connect via New Query, I'll see both connections from the table and data model.

Do anyone know why some of the data models can be detected while some have to be loaded as the table before it can be detected?

Thanks

[CK76]

I think you are mixing up few things.

1. Data model is associated with PowerPivot. Connection can be brought directly into data model or can be brought in via PowerQuery and then loaded to data model.
2. Those that are directly brought into PowerPivot data model are not present as Query (since it is technically not part of PowerQuery's process).

For consistency sake, and for ease of transforming data etc. I'd always recommend creating connection from PowerQuery (Get & Transform), then loading it to data model.

[Ohhaa]

Thanks @CK76

My bad is 930pm now ... You're right, i'm trying to connect via PowerQuery but as mentioned, i can't seem to see all the connections from some of the files whereby I load the data.

Which is why, for those i can't do a connection, I load onto Sheet instead of Connection and somehow this works. But when i connect to the new Workbook, i'm seeing both the Data model & the same set of data i loaded onto the Sheet (Is not really a big issue yet since is still less than 1.6mil rows of data)

I guess my question here is, why some of the workbook works and some dont?
Hope i'm explaining myself clearly .

[CK76]

Are some files exported from some external program?

I ask, since some 3rd party software that claims to export data as Excel file, actually exports as HTML file with .xls/.xlsx as extension.

Those file will need to be connected to differently than normal Excel file and requires extra step (I remember posting about it a while back, but can't find the thread right now).

Other than that, I've never encountered issue where PQ wasn't able to connect to Excel file.

[Ohhaa]

Nope, all the 5 files are export via PowerQuery and everything is done within Excel thus is I'm even more curious how this is happening.

After which i created a Master file (Excel) and connect all 5 files there

[CK76]

If you can upload sample workbook where connection is failing. I can take a look at the cause. Other than that, I'm out of ideas.

[Ohhaa]

Sure, i can try sending you the files . Let do this offline, what is your email ?

Thanks alot

[CK76]

Ok, PM has been sent with email address.