cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
msellner_1025
Frequent Visitor

Use Result of Query 1 in Query 2

Hello!  

 

I have a query that returns a random file name that I need in order to download a csv formatted reported. Below is the power query that I have set up – it returns the file name as expected and is working great! 

msellner_1025_0-1669642688160.png

 

The result is always one value in the first row/column (see example screenshot below). 

msellner_1025_0-1669642812806.png

 

I need to run a second query that uses the random file name generated from the first query. Currently, I am manually pasting this value into the second query after I run the first query and it returns the random file name. Below is the second query I am using – I highlighted the variable value that I paste in from query one. 

 

msellner_1025_1-1669642739239.png

 

How can I set up the second query so that it will use the result of the first query and not require me to paste in the new random file name value each time? 

 

Thank you!

1 ACCEPTED SOLUTION

Hi @msellner_1025,

 

According to the format of what you say works the line should look like:

 ""fileName"":""" & Expensify_PullDataRequest & """

Otherwise you either missing the "" ( ""fileName"":Expensify_PullDataRequest) or send Expensify_PullDataRequest as value instead of  is_reconciliation_2816079167651847981.csv.

 

Cheers,

John

View solution in original post

14 REPLIES 14
msellner_1025
Frequent Visitor

The fileName is not formatted correctly.

msellner_1025_0-1669645270911.png

 

Thanks,

Mikail

 

It may be because you surrounded it with curly braces ( { } ). Try using it just as ""fileName"":"csvFile"

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete 

That was my initial thought, but when I try that, I get the following error.

msellner_1025_1-1669646862895.png

 

Mikail

 

Does it work just as ""fileName"":csvFile ?

Sorry, I'm not great with API syntax so not sure what the valid options are.

 

If that doesn't work, can you send a screenshot of the Expensify_PullDataRequest query result please? It may be an issue with how this is being output.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete,

I tried ""fileName"":csvFile and it returned the same error message. Below is the result of the Expensify_PullDataRequest query.

msellner_1025_0-1669648286752.png

 

If I copy and paste this value into query 2 like ""fileName"":""is_reconciliation_2816079167651847981.csv"", it works great. I'm just not sure how to format this as a variable in power query.

Mikail

 

Hmm, ok, that seems fine. This should be working 🤔

What if you skip the variable declaration and just go straight for ""fileName"":Expensify_PullDataRequest (and other variations of "E_PDR", ""E_PDR"" etc.)?

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete ,
Before submitting this post today, I was previously using ""fileName"":Expensify_PullDataRequest and any variation I could come up with. I worked on this for hours with no success. I thought declaring a variable would solve the issue, but it appears there is not a way to declare a variable in JSON body text using Power Query. I really want a solution for this now that I have invested so much time.

Mikail

Hi @msellner_1025,

 

According to the format of what you say works the line should look like:

 ""fileName"":""" & Expensify_PullDataRequest & """

Otherwise you either missing the "" ( ""fileName"":Expensify_PullDataRequest) or send Expensify_PullDataRequest as value instead of  is_reconciliation_2816079167651847981.csv.

 

Cheers,

John

Hi @jbwtp,

 

Thank you so much! This is exactly what I needed and it worked like a charm.

 

Thanks again,

Mikail 

 

@msellner_1025 I think @jbwtp has it correct here: you'd need to send the quotations as part of the command, something like this:

"" & Expensify_PullDataRequest & ""

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




msellner_1025
Frequent Visitor

@BA_Pete,

 

Thank you in advance for your help!

 

How do I reference the floating value? I think my code is applying the variable incorrectly as I get an error message when I run it now.

msellner_1025_0-1669644396190.png

 

Thanks!

 

What error do you get?

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete,

 

Thank you for your help! The format """ & Expensify_PullDataRequest & """ worked! So happy to have this figured out!

 

Thank you,
Mikail

 

BA_Pete
Super User
Super User

Hi @msellner_1025 ,

 

Right-click on your random file name cell and select 'Drill Down'. This will convert your table value into just a floating value.

At the start of your second query, declare this as a variable using just

'variableName = nameOfRandomFileNameQuery'.

Then you should be able to apply this variable in your code instead of the hard-coded file name.

 

Pete

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors