cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
datasetleo Regular Visitor
Regular Visitor

Power Query - Excel - Passing a variable to the middle of URL datasource

Hi, All!

 

I drilled down an item in a query, which I'd like to add it to datasource's URL to make it dynamic.

I can't find the right syntax. can anyone please help how to pass the variable?

Screenshots below - drill down query is called [aaa_report] and I wanted to add it to replace the month in the URL  (which in this case is june_2019)

 

In addition, is there any way to ensure this drill-down query gets called first once refreshed all?

 

Thank you!

 

Annotation 2019-08-01 182335.pngAnnotation 2019-08-01 182425.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Power Query - Excel - Passing a variable to the middle of URL datasource

assuming the query that returns june_2019 is named prmQuery, the path string should change from

"d:\my Documents\project\june_2019\df\total.csv"

to

"d:\my Documents\project\" & prmQuery & "\df\total.csv"

if other queries are dependant on it PowerQuery will manage the order automatically

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User II
Super User II

Re: Power Query - Excel - Passing a variable to the middle of URL datasource

assuming the query that returns june_2019 is named prmQuery, the path string should change from

"d:\my Documents\project\june_2019\df\total.csv"

to

"d:\my Documents\project\" & prmQuery & "\df\total.csv"

if other queries are dependant on it PowerQuery will manage the order automatically

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

datasetleo Regular Visitor
Regular Visitor

Re: Power Query - Excel - Passing a variable to the middle of URL datasource

Thanks @Stachu 

 

It looks like I am missing something; when I added query in "& query &" - I got firewall exemption. I do have access to the file obviously - any way to circumvent this error? Thanks again!

 

project.png

Super User II
Super User II

Re: Power Query - Excel - Passing a variable to the middle of URL datasource

the reason are security settings:

https://docs.microsoft.com/en-us/power-bi/desktop-privacy-levels

you need to change them to be consistent for the data sources you mix together (e.g. all organizational, depending on your requirements), it's all explained in the article

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

datasetleo Regular Visitor
Regular Visitor

Re: Power Query - Excel - Passing a variable to the middle of URL datasource

@Stachu , BIG THANK YOU! Smiley Happy

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors