How To Automatically Pull In New Report Data Into Excel Using PowerQuery

One common situation most users of Excel face in making recurrent reports -- daily reports, weekly reports, monthly reports etc -- is that of updating the raw data part of the report with the new data for the period they are creating report for.

As simple as this looks, it is often a boring, repetitive and time consuming task. It could mean having to copy and paste data from the new source data files every day. For some people who get data from different sources (branches, sales people, departments etc), that coping and pasting can easily become a 20 mins task with potentials for human errors.

How do you set up your report to automatically pull in new data from source files? PowerQuery is the answer.

[franks]

Well explained and very valuable. Thank you!

[karladuenas]

Thanks for posting this video, I will keep browsing for more videos to help me with my redundant report processing. God bless

[bzflowerbee]

Thank you for sharing Michael, it's very helpful. I do have a question though. Let say, I have to run a report every month. Since this report doesn't have storage location, I use the power query to merge with another file that has storage location. (Kind of like a vlookup). So, now my report has the storage location I want. Then, next month, I run the report and I have to do the same thing, to create a query to merge with another file so I can get the storage location. It's repeating every month. Can I use the power query on this matter? I don't want to combine the file though, this is the individual monthly report I need every month. Thanks for your time.

[irishdragon]

Thanks, this is really helpful! I apologize if this is a dumb question but I have excel files saved daily to a folder. The files have some text on the top that I don't want to pull in. Is there a way to edit that and format it once so it pulls just the data every time I need to refresh it? Hopefully that make sense

[traceylu]

Thank you so much, This video really helps me with my work.

[kipasangin]

Hi.if the repetitive data source is from an auto email how to.automate.it with power query?

[Ola-iuzc]

Nice one sir.
Do I need to delete old excel files and put new files daily.If yes will Power query retain the old data.thanks

[HarukiYamamoto]

I get an error saying "external table not in the expected format." What does that mean and how do I clear the error?

[HoangNguyen-tbdk]

Thank you, but when I close the excel file and reopen it, it hasn't worked when I refresh, even if I want to change in Power Query, it hasn't worked anymore
When I want to get more data or excel file or folder. It hasn't worked anymore.
Please show me the way to handle it. Thank you so much.

[Maldive]

Hi there, this is amazing. I do have a question. i have already loaded 9000 records into the power pivot and built a dashboard based on it, it is a monthly report. but now problem the is, each month i have new sales data. in that case, how to just load or add the data to the table that 's already in the power pivot??? cause i tried many ways, it would just add the data to a different tab, like a new table, but that is not how i want it. please help. thanks in advanced.

[beewhyfocus]

Good one. But I can't see the video properly from my phone. The zoom level is not visible on phone. May be I will try watching on laptop. Or better still consider this when making subsequent videos. Thank you.