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.

[@karladuenas441]

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

[@franks8408]

Well explained and very valuable. Thank you!

[@christopherzeller2098]

Very structured! Thanks

[@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.

[@SimX9000]

Thank you Sir! Well done

[@shawnbatty2211]

Awesome! Thank you. Truly helpful.

[@rakeshmahakud110]

Awesome explanation

[@traceylu5283]

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

[@MichaelOlafusi]

Glad it helped!

[@Maldive177]

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.

[@peteadrian7743]

Yes! Exactly what I'm struggling with. What's the point otherwise?

[@carlanthonycaraan7807]

Did you get any solution for this? I am currently having the same chalange. Will you please share if you have any answer to this please thank you.

[@swapnilpawale5625]

You have to convert your data into Excel's standard table. Refer tabel in insert tab of excel. You paste your data below excel's standard table. Pivot will automatically consider the same. You don't need to refresh data or play with data everytime.

[@irishdragon2637]

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

[@Ola-iu6zc]

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

[@HoangNguyen-tb7dk]

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.

[@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.

[@MichaelOlafusi]

Hi Adebayo. It's RUclips that's auto-setting the video quality, usually based on internet connection strength/bandwidth. You can set the video quality level to HD, that way it shows in higher clear quality,

[@kipasangin2782]

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

[@andreeaj9172]

Cool cool cool! Thanks

[@dinahany233]

Mercii

[@HarukiYamamoto]

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