PowerQuery | Load files based on cell reference

[rachel06]

Hi!

I've got a query almost all worked out.

I'm trying to replace the "Source=Folder" to reference a cell (in my case Cell C3 that I have named "FilePath") rather than a specific file path but I'm getting an error.

This is what I found on another thread, but it's not working for me. Please note that these two lines are replacing one that looks like this:

Source = Folder.Files("C:\Users\MyFiles\Desktop\MyClient\Feb Reports")

The error I'm getting is

Expression.Error: We cannot convert the value null to type Text.

Details
Value =
Type=[Type]

let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath), null, true),

Looks like the rest of the code is just the columns I deleted or edited so I don't think that's important for this, but any help on how to resolve would be much appreciated!!

 

[johnny51981]

Is your FilePath a file path INCLUDING a File Name and its Extension? OR does it still stop after \Feb Reports like the original Source step?

The way that your code is currently written, you are trying to access a Workbook/File. If your FilePath named range is only to a Folder, then that is where your error is coming from.

If you need to look at a Folder's files first, before actually getting into the file(s), then it should look a bit more like this:

let
    FilePath= Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    Source = Folder.Files(FilePath)

If it is a specific file, it would be this...for a CSV:

let
    FilePath= Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(FilePath)),

and for a XLS(X):

let
    FilePath= Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath)),

 

[rachel06]

Hi!
It doesn't include a file name - there are multiple workbooks with a single worksheet that need to be consolidated. Does that make a difference? I think that's also in the rest of the code which just kind of seemed like noise that wasn't needed for my question!

 

[johnny51981]

It would. You are currently coded to search for an individual file. "Excel.Workbook(File.Contents(FilePath), null, true),"

You will want to go with this option (note how it says Folder.Files):

let
    FilePath= Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    Source = Folder.Files(FilePath)

Then the steps following should then take you exact file that you are needing to go...I'm assuming.

 

[rachel06]

Thanks! The good news is that worked. The bad news is I'm still running into the issue of Excel running out of memory. Might just have to split these batches of files up!

 

[johnny51981]

Oh its YOU!!!!! Hi again! Bummer. However, are you still trying to load the full dataset that exceeds the million-ish row records or have you been able to filter a bunch of it out before it loads to the table on the worksheet?

 

[rachel06]

Hi! Trying to do the million+ rows. I got the big N-O on deleting rows before they're loaded. Not sure I noted this previously but these also have 226 columns lol. They're just enormous files. I was hoping to just load it all to the data model, then cut the columns out and pivot but no dice. Dusting off my Access skills. It's been a decade but I'm hoping I can get something to work!

 

[johnny51981]

<cracks knuckles> Access is where its at!!! I miss it sometimes.

Continued luck!

 

[rachel06]

LOL It's too big for Access too. Argh. They're going to have to be either okay with smaller files or splitting the batches up.

 

[RoryA]

Do you have 32 or 64 bit Office?

I assume you are only loading this to the data model, and not to a worksheet?