Add named range from individual files to Folder source in Power Query

[cb366374]

I have a folder of files that I am querying for the files' date modified, file size, and a few other attributes. Long-story-short we have multiple versions of these files in subfolders and each "release" we make a new folder and copy the Excel files into them. So I created this query that can track which files are different from version to version (it's basically a versioning tool). However, my issue is there are more granular version numbers stored in a named range within each file that I would like to access. The cell is named the same in every file (let's called it "VersionCell"). So what I'm trying to do is add the contents of this cell to the table as a new column/attribute.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Already Exists[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]Need this one added[/TD]
[/TR]
[TR]
[TD]FileName[/TD]
[TD]Date Modified[/TD]
[TD]Date Created[/TD]
[TD]Full Path[/TD]
[TD]VersionNum[/TD]
[/TR]
[TR]
[TD]File1[/TD]
[TD]10/31/2016[/TD]
[TD]10/31/2016[/TD]
[TD]C:\\Version10\File1[/TD]
[TD]10.1[/TD]
[/TR]
[TR]
[TD]File2[/TD]
[TD]10/29/2016[/TD]
[TD]10/29/2016[/TD]
[TD]C:\\Version11\File2[/TD]
[TD]11.0[/TD]
[/TR]
</tbody>[/TABLE]

The files are rather large so I'm not sure if it's possible to fit this in the same query (or if it's even possible for smaller files). The only experience I have with this type of query was super simple...

let    Source = Excel.CurrentWorkbook(){[Name="ThirdRange"]}[Content],
    ThisCell = Source{0}[Column1]
in
    ThisCell

This seems like one of those things that has to be possible but I can't figure it out. I'm fairly new to Power Query so if someone can point me to a helpful resource, that would help also. Thanks in advance!

Also, here is the current query. I stripped a bunch of stuff out to make it simpler to view. Just a basic folder query but you can see where I remove the content column that stores the file.

let    Source = Folder.Files("path goes here"),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Content"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Extension] = ".xlsx")),
    #"Expanded Attributes" = Table.ExpandRecordColumn(#"Filtered Rows", "Attributes", {"Archive", "Compressed", "Content Type"}, {"Attributes.Archive", "Attributes.Compressed", "Attributes.Content Type"})
in
    #"Expanded Attributes"

 

[billszysz]

Hi cb,
Add this step after your last one.

 #"Added Custom" = Table.AddColumn(#"Expanded Attributes", "Version", each Excel.Workbook([Content]){[Name = "VersionCell"]}[Data]{0}[Column1])

Regards

 

[cb366374]

That's great, thank you!

It didn't work at first because I was removing the content column. I moved that step to the end and it works. However, it still makes the query very slow. I sped it up some by breaking it into multiple queries and only pointing to the subfolders I need but it still takes about a minute to update. Any idea on how to speed that up?

 

[cb366374]

Basically, is there a way to do this without loading the entire file like it appears to be doing now?