I’m looking for some help on the best way to bring my data into the Query Editor.
What I’m working with is Excel files dated from 2011 - 2018 (8 files). These files are store locally on my desktop. Each file has 12 tabs for the months of the year. Most tabs only contain about 300-350 rows of information. What I want to do is get all this into the Query Editor so I can analyze the info in Power BI for my Sales VP.
So my questions is - which of the following 2 ways is the best way to attack this??
I open Query Editor and get my data. I bring in each of the tabs (Jan, Feb, etc.). I then run all the transformations on each tab to get the data like I need it. I make a reference of the Jan data and rename it to the year (2011, etc.). I then append the rest of the tabs to the year table and mark the others to not load and move them to the Non-Loading Group I created.
I open Query Editor and get my data. I bring in each of the tabs (Jan, Feb, etc.). I make a reference of the Jan data and rename it to the year (2011, etc.). I then append the rest of the tabs to the year table. Then I run the transformations on the data that I need. Then mark the un-needed tabs to not load and again move to the non-loading group.
These methods while similar are quite a bit different. I did method #1 to my data and a refresh takes forever. It will refresh but it was over 30 minutes to complete.
Looking for advise from the Community on how to best handle this situation for speed and efficiency.