Include Excel files in query based on NamedRanges

[o0omaxrbi]

Hello,

I have a folder with my Excel Files. In each of those Excel files, there is a Named Range called "MyRange". I only want to load those Excel Files, where the value of the named Range "MyRange" is TRUE. How do I do that in PowerQuery?

I somehow tried to get to the Named Data with adding a custom column in Powerquery which should do the following: Excel.CurrentWorkbook(){[Name="Ablaufdatum"]}[Content]. But it does not work. Can somebody help me?

Thanks
Max

 

[jdellasala]

Power Query can do that. From the Data tab, select Get Data -> From File -> From Folder and select the folder with the files needed. That will result in a list of all the files like this:

Clicking the [Transform Data] button will bring you into the Power Query Editor with a list of files like this:

If you have unwanted files, filter them here. I suggest using Transform -> lowercase on the Extension column, and filtering it for only .xlsx extensions to prevent proceeding with any unwanted files. If the file names have a pattern, filter the Name column for just those files. It works just like a Table.

Now at the Top Right corner of the Content column click on the double down arrows at the top right corner of the column

and you'll be presented with view of one of the files which will be the Sample File. If all of your files have a range named MyRange, you'll see this:

Select MyRange and Power Query will pull in that range from all the files in the folder and append their data into one Output query with the same name as the folder:

Files that do not have that named range will not be brought in. You'll notice that the first row was not promoted to header. Do not change it in the final output query. Instead select the Transform Sample File query and promote the first row there. Also rename any columns there. NOTE: any files with a different structure - different column names, more columns etc. will result in something you may not want or break the query preventing any output.

There are plenty of Playlists on YouTube on Power Query like the ones from Mr. Excel and Mike at ExcelIsFun which includes before and after sample files. Power Query is a relatively easy skill and once learned will save you hours of work!

Good luck.

 

[jdellasala]

Hello,

I have a folder with my Excel Files. In each of those Excel files, there is a Named Range called "MyRange". I only want to load those Excel Files, where the value of the named Range "MyRange" is TRUE. How do I do that in PowerQuery?

I somehow tried to get to the Named Data with adding a custom column in Powerquery which should do the following: Excel.CurrentWorkbook(){[Name="Ablaufdatum"]}[Content]. But it does not work. Can somebody help me?


View attachment 77188

Thanks
Max

Max,

Thanks for the checkmark! One thing I should have mentioned is that the Query labeled Transform Sample File is basically the template for what happens to files pulled in. Changes like a sort or final touches can be made on the final Query (Junk), but if you find something that can be done to all the files, it's best to do it to the Sample File Transformation.