Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to 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
The fileName is not formatted correctly.
Thanks,
Mikail
It may be because you surrounded it with curly braces ( { } ). Try using it just as ""fileName"":"csvFile"
Pete
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
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
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
Proud to be a Datanaut!
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!
What error do you get?
Pete
Proud to be a Datanaut!
Thank you for your help! The format """ & Expensify_PullDataRequest & """ worked! So happy to have this figured out!
Thank you,
Mikail
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
Proud to be a Datanaut!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
56 | |
27 | |
17 | |
13 |