cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

relative file reference

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: relative file reference

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)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

12 REPLIES 12
Highlighted
Community Champion
Community Champion

Re: relative file reference

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

Highlighted
Helper III
Helper III

Re: relative file reference

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

Highlighted
Helper III
Helper III

Re: relative file reference

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!!

 

Highlighted
Super User III
Super User III

Re: relative file reference

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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Highlighted
Super User IV
Super User IV

Re: relative file reference

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper III
Helper III

Re: relative file reference

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"

 

Capture.JPG

Highlighted
Helper III
Helper III

Re: relative file reference

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

 

 

Capture.JPG

 

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"

Highlighted
Super User IV
Super User IV

Re: relative file reference

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper III
Helper III

Re: relative file reference

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors