relative file reference

[sshweky]

Hi - I am trying to pull data from a dropbox file in Power Query. However, every user has a different path to that dropbox file. Any ideas on how to use dynamic file references in the advanced query editor?

A couple more questions...

(1) Is there a function I can use to return the name of the current directory?

(2) Is there a funnction I can use to go up or down one level in the directory structure?

Thank you!!!

Steven

[ankitpatira]

@sshweky You can use newly introduced parameters functionality to dynamically switch data sources. 

[sshweky]

Do you have an example or soemthing I can read? I am not familiar with this?

[sshweky]

I am working in Excel 2016 with Power Query (not PowerBI). Here is a little bit more detail of the way I am doing it ...

So far I have an excel file with a table called "MyPath" which stores each user's path. The value in my "MyPath" is "C:\Users\Steven\Desktop\Dropbox". How do I retrieve that path, combine it with the file name "\dashboard\fc.mdb" and use that entire file name to build a query in my main template file?

I tried to build a query (called PathQuery) to retrieve the path from "MyPath.xlsx" & then combine the path & the file name, but I get an error that I can't combine a Type List with a Type Text.

lpath= PathQuery[MyPath],
lfile = Text.Combine({lpath, "\FC\dashboard\fc.mdb"}, ""),
Source = Access.Database(lfile),

Thank so much for your help!!

[Seth_C_Bauer]

So many questions on Power Query and M... so many things to learn. In the meantime, it's nice to know people like @ImkeF. Maybe she can help out with this.

[ImkeF]

let me shortly explain the error-message:

Ipath is a list, so you would have to decide somewhere, which item from the list should be taken. Also that is the reason why you cannot use Text.Combine on it, because you can use this command only with text as input, not with lists.

If you're working in Excel, you can try my dynamic approach instead:

So after you've extracted the individual dynamic filepath in a query named "Filepath" (which should be a table with one row only) you can construct your dynamic filepath like this:

lfile = Text.Combine({Filpath[TheColumnNameWithPathInIt]{0}, "\FC\dashboard\fc.mdb"}, ""),

So the key is to adress the content of the table like in Excel via 2 coordinates: Columnname (here. "TheColumnNameWithFilepathInIt") and the rownumber (here: 0, as PQ starts to count with zero).

I find this article very helpful on this matter.

[sshweky]

Youa re amazing! Thank you!!! I just got stuck at the next step. Below is my revised code. The problem is that the Source function returns an error that it cannot convert my path to type Binary?

let
lfile = Text.Combine({FilePathQuery[MyPath]{0}, "\FC\dashboard\fc.mdb"}, ""),
Source = Access.Database(FileContents(lfile), null, true),
_invdtl = Source{[Schema="",Item="invdtl"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(_invdtl,{{"INVDT", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [ACCT] = "06074")
in
#"Filtered Rows"

[sshweky]

OK. I think I got passed the binary issue. Revised Code below. Now I get this strange error when trying to filter the rows?

let
lfile = Text.Combine({FilePathQuery[MyPath]{0}, "\FC\dashboard\fc.mdb"}, ""),
Source = Access.Database(File.Contents(lfile)),
_invdtl = Source{[Schema="",Item="invdtl"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(_invdtl,{{"INVDT", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [ACCT] = "06074")
in
#"Filtered Rows"

[ImkeF]

You can either enable fast combine: https://support.office.com/en-us/article/Privacy-levels-Power-Query-cc3ede4d-359e-4b28-bc72-9bee7900...

or split your query by checking step "Source" in the editor - rightclick your mouse and choose "Extract previous". This will create a separate query for the first steps. That way the query referencing external sources are separated from the one combining internal stuff and the message should go away.

[sshweky]

Thanks so much for your help!! I tried it but I still get the same message?

Revised code...

let
 lfile = Query2,
 Source = Access.Database(File.Contents(lfile)),
 _invdtl = Source{[Schema="",Item="invdtl"]}[Data]
in
 _invdtl

[sshweky]

the split didn't work, but the privacy settings did. THANK YOU!!!!!!

[apanichi]

Well, this realy help us to do something about the issue?

[ImkeF · Accepted solution]

Hm, strange.

Please try another split on _invdtl 

Otherwise please try if the other option works (just to check if there isn't anything else to it here, or a bug)