Power Query: Connect to Files in a List

Power Query: Connect to Files in a List | Excel Off The Grid

Рет қаралды 5,632

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Check out the blog post★
exceloffthegrid.com/power-que...
★ About this video ★
Power Query has a really powerful feature that lets us combine all the files in a folder. However, sometimes the files are not contained in a single folder. Sometimes they are spread out in multiple places. How can we deal with that?
That's what we are looking at in this video. How to combine files based on a list.
0:00 Introduction
0:31 Connect the files in a list
5:13 Conclusion
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel

Пікірлер: 49

@Traybo1974 4 ай бұрын

Simple solution, perfect! It was exactly what I was trying to for trial balance files!

@ExcelOffTheGrid 4 ай бұрын

Good work - hopefully it can save you a bit month end time.

@user-tg1vt5tw8c 5 ай бұрын

ماشاء الله تبارك الله عمل رائع جدا ومشكور جدا جدا وربنا يهديك

@mohammedelsakally540 7 ай бұрын

Brilliant tricks to combine various files 👌...

@user-rt1ub9mi5t 5 ай бұрын

This is the best tip I ever got. I have 47 Excel files (of assorted xls, xlsm and xlsx extensions...not ideal, I know....) in a folder where I named three ranges Baseline | Claim | Cost (I couldn't Ctrl-T Tables and did not want to go the Sample file in Power BI). So I Unpivoted and copied one Let statement from one query. But instead of "Sheet" I used Source{[Item="Claim",Kind="DefinedName"]} . Then made 2 copies of the same query only changing my "DefinedName" (To "Baseline" and "Cost" in my case). And it worked. Brilliant! Thank you Mark! I will definitely be following you!

@ismaelkourouma5558 Жыл бұрын

Excellent Mark. Thanks

@ExcelOffTheGrid Жыл бұрын

Thanks Ismael 👍

@kris081988 10 ай бұрын

Exactly what I needed. Thank you!

@ExcelOffTheGrid 10 ай бұрын

You're welcome! 👍

@republikadugave420 5 ай бұрын

I mean wow...this is a special video..great job

@flyingHubby 11 ай бұрын

So simple and very helpful

@ExcelOffTheGrid 10 ай бұрын

Glad to hear that! 😁

@mohammedelsakally540 9 ай бұрын

Thank you very much!

@ExcelOffTheGrid 9 ай бұрын

I hope you can put it to good use.

@IvanCortinas_ES Жыл бұрын

Fantastic Mark. Excellent resolution with Power Query. As always contributing light in this matter. Thank you.

@ExcelOffTheGrid Жыл бұрын

Thanks Ivan 😀

@Softwaretrain Жыл бұрын

Woow, I really loved that you added all of M codes inside custom column. Thanks Mark.

@ExcelOffTheGrid Жыл бұрын

The ‘let’ and ‘in’ combination creates a pseudo custom custom function with no parameters. I’ve been playing around with this recently, it’s a really nice feature which had never occurred to me before.

@urosmaksimovic 10 ай бұрын

Excellent idea 👍

@ExcelOffTheGrid 10 ай бұрын

Glad you think so!

@patrickschardt7724 Жыл бұрын

A few weeks I needed to do just this. I couldn’t find a video or tutorial about combining files from multiple folders. I eventually made a custom solution but I think it’s long and complex. This is a lot simpler

@ExcelOffTheGrid Жыл бұрын

I’ve made similar complex solutions in the past. But I discovered this easy method and wanted to share it. I hope you can use it somewhere.

@rigobertogarcia Жыл бұрын

Excellent, Mark. Explanation, clear, precise and simple and that will save us a great amount of time in the transformations that must be made to the data for its analysis. Thank you very much for sharing such valuable information with us.

@ExcelOffTheGrid Жыл бұрын

When I stumbled across this technique, I was surprised at how simple it was for dealing with complex situations. I hope you can put it to good use.

@kebincui Жыл бұрын

Super as always👍. Thanks Mark

@ExcelOffTheGrid Жыл бұрын

Thanks Kebin. I appreciate your support 😁

@GrainneDuggan_Excel Жыл бұрын

Mind blown! What a great solution - thanks Mark

@ExcelOffTheGrid Жыл бұрын

I’m glad you like it. I can’t believe I’ve not thought of it before. 😀

@Rice0987 11 ай бұрын

Cool! I will check my similar solution. Thanks!

@ExcelOffTheGrid 11 ай бұрын

Don’t go throwing out your solution. There are lots of ways to achieve this. In my opinion, it is the easiest to apply, but not necessarily the easiest to debug.

@Rice0987 11 ай бұрын

@@ExcelOffTheGrid in my project there are always four files only, but they are always have different location, i need to know only filename, and somehow (i dont remember now) i get full path to each file and get combine their data. But i always look up for any solution, may be i'll find something useful foe my work. So, thank you for sharing your method!😊

@ExcelUnlocked Жыл бұрын

Dear Mark, this is awesome as usual, thank you for sharing.👍

@ExcelOffTheGrid Жыл бұрын

Thanks for watching, I’m glad you enjoyed it.

@Karen-bm3rj Жыл бұрын

Excellent, thanks

@ExcelOffTheGrid Жыл бұрын

You are welcome Karen . 👍

@robrayborn1349 Жыл бұрын

Bloody clever!

@ExcelOffTheGrid Жыл бұрын

Thanks - I hope you can put it to good use.

@sherryizzie5309 Жыл бұрын

That’s really a cool and useful trick!!

@ExcelOffTheGrid Жыл бұрын

I'm glad you think so. I hope you can put it to good use.

@Dan_De_Man Жыл бұрын

Great video Mark. Is it possible to do a simular thing and combine data from a list of folders if they are all over the server

@ExcelOffTheGrid Жыл бұрын

It’s possible, but the approach would be slightly different because the connectors are different.

@JenMayB Жыл бұрын

Amazing.

@ExcelOffTheGrid Жыл бұрын

Thank you! Cheers!

@vacilando86 Жыл бұрын

That's awesome

@ExcelOffTheGrid Жыл бұрын

Thanks 😀

@dhanrajs7913 Жыл бұрын

Your video was very helpful but, I want to ask you about, the VIEW>DATA Preview option isn't working even though I have Updated the Version.

@arungupta8444 Жыл бұрын

Hi, if there is a set of different Excel files and there are data transformation steps applied to each of them them individually and then all the files are further connected with steps like merge, combine and group. Now there is an update in one/all Excel files in terms of data (columns remains same)and it is not possible to update each and every column value, in this case how toreplace the original Excel file/files (keeping the same file name) so that the all the steps can/will be applied as it is after file replacement on the new file and the outcome will be based new files

@jerrydellasala7643 Жыл бұрын

Why not create the file list using Power Query - no typing, filtered list as needed?

@ExcelOffTheGrid Жыл бұрын

As I mentioned in the introduction, this is for when the files are disbursed all over the place, with a mixture of file names, sheet names, and file locations. The example uses a simple file structure, because I’m too lazy to create file chaos just so I can ignore it.