Use Result of Query 1 in Query 2

[msellner_1025]

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! 

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

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. 

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!

[msellner_1025]

The fileName is not formatted correctly.

Thanks,

Mikail

[BA_Pete]

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

Pete

[msellner_1025]

@BA_Pete 

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

Mikail

[BA_Pete]

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

[msellner_1025]

@BA_Pete,

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

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

[BA_Pete]

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

[msellner_1025]

@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

[jbwtp · 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

[msellner_1025]

Hi @jbwtp,

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

Thanks again,

Mikail 

[BA_Pete]

@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

[msellner_1025]

@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.

Thanks!

[BA_Pete]

What error do you get?

Pete

[msellner_1025]

@BA_Pete,

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

Thank you,
Mikail

[BA_Pete]

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