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
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