Looking for a Best Practice that's speedy

[GuyJohnson]

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

Method #1

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.

Method #2

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.

[sam.mckay]

Ok, I’ve tried my best to imagine this. This is what I advise. I’m presuming that there is a date identifying in every table of the data. If you can do this it will make life a lot easier.

I would first bring in each file separately, but make sure to consolidate each tab on each one, so that it because all on query for each year.

I would then consider all of these ‘staging queries’

From here you’ll have 8 staging queries for each year.

Then append them all into one query and then call this main data table (fact table)

I would then make your transformation once here. Try to avoid doing them until you get to this point. This will prevent any duplication of effort.

Do you think this will work?

[GuyJohnson]

Yes each original has a date for each entry and I use your Data code for my main date table.

So if I understand you I should append each month into a year; then append all the years into a main fact table; then do all the transformations.

That makes sense and I’ll give it a whirl next week to see what happens.

What you say makes sense and I guess I was looking for some other input to give me some needed direction.

[GuyJohnson]

I followed the suggestion that Sam provided and was able to get everything up and running and pushed to the Service in about 2.5 hours. The original method I used took me about 3 days.

Still need some configuring of the pages for aesthetics but the basics are there.

Thanx Sam

[sam.mckay]

Great stuff