Share
Report
2Hi - the context of the issue is I have an Excel file of about 20 worksheets with data structured in the same format across all worksheets, loan identifiers down the left hand side then time across the top, with a different value on each worksheet (e.g income, balance, paydown etc).
At the moment I have a separate query going into each of the worksheets, I then do some transformations to get the data into a format needed, so that I have a table of loan identifiers with the value for the line item in that worksheet, e.g. income. Then I merge each worksheet to get the line items all in one table, so I can then use them in Power BI tables to slice and dice the loan book.
However it's taking ages to set up:
- having to set up 20 merges to combine the different worksheets to get the line items in one table is very cumbersome. Is there a way of combining multiple queries in one go?
- having set up all the queries to get the data from each of the worksheets, which seem to have loaded as the previews are available, whenever I try to set up merges of the queries or make any changes to the queries, it goes off and loads the data from the source Excel file again. I would have thought it would have loaded it into memory once and wouldn't need to reload it each time. The issue with this is that it's c.90mb so takes a while for the file to load slowing the whole process down enormously.
Any thoughts on this? Not sure if I've structured this in the most efficient way, so any pointers on how to set this up would be much appreciated!
Thanks,
Matt
All responses (
Answers (
