What's the best way of pulling data from the data model of another Excel workbook via PowerQuery?

[alex1alex]

The set up is that I have an excel workbook where I do "stuff" in the Data Model.

I'd like to use that stuff as an input for another Excel workbook.(is this a common setup?)
Up until now I've been doing one of the following:
- Loading that particular sheet of the data model back into the workbook as a table (via the Load To)
- Using a PivotTable to grab a sub-set of the data model (and either querying it via PowerQuery dicrectly or saving it as CSV and querying that file.)

For all of the above, the Con is that, since you are loading the data as text back into the Worksheet the size of the worksheet balloons. Additionally, it's really time consuming to go and do a refresh. Using the PivotTable to grab a subset of the data model takes less space (since I can filter down how much data I pull back.) But it's sort of fiddly. (you don't get a nice structured table and it's still pretty big. Also, it seems like an needless step.)

Ideally, I'd like to query the data model directly from PowerQuery.
Alternatively, I'd like to be able to filter the Load To table so that I can specify the columns and rows that are pulled in.
Or something else I hadn't though up

Looking forward to hearing ideas.
Kind Regards,
Alex

 

[Matt Allington]

You can use DAX Studio to connect to the datamodel in your main spreadsheet. Write a query to get the table you need and export it to csv. You can then bring it back into another workbook via PQ.

 

[alex1alex]

Interesting...

Can you recommend a getting started with DAX Studio tutorial? (I've read about it but couldn't find anything on youtube....and last time I tried using it wasn't intuitive enough for me to figure it out on my own.)

You can use DAX Studio to connect to the datamodel in your main spreadsheet. Write a query to get the table you need and export it to csv. You can then bring it back into another workbook via PQ.

 

[Matt Allington]

This may help to get started with DAX studio
https://daxstudio.codeplex.com

it's not perfect.

Then this

https://www.simple-talk.com/sql/reporting-services/using-dax-to-retrieve-tabular-data/

maybe be I will do a tutorial, but probably not in your time frame - sorry.