Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need help converting a Rest API to be useable in Power Query. The issue I am having is with the --data-urlencode Template as I get a "Response Message: No Template Submitted".
Code request is copied from Postman which completes the request as expected by producing a "filename.csv", which then is downloadable (I have a Downloader API set-up in Power Query that works great at retrieving the files):
curl --location --request POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription=
{"type":"file",
"credentials":{
"partnerUserID":"username",
"partnerUserSecret":"password"
},
"onReceive":{
"immediateResponse":["returnRandomFileName"]
},
"inputSettings":{
"type":"combinedReportData",
"reportState":"APPROVED",
"limit":"10",
"filters":{
"startDate":"2019-01-01",
"endDate":"2019-10-01"
}
},
"outputSettings":{
"fileExtension":"csv"},}' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'template=
<#if addHeader == true>
Merchant,Original Amount,Category,Report number,Expense number<#lt>
</#if>
<#assign reportNumber = 1>
<#assign expenseNumber = 1>
<#list reports as report>
<#list report.transactionList as expense>
${expense.merchant},<#t>
<#-- note: expense.amount prints the original amount only -->
${expense.amount},<#t>
${expense.category},<#t>
${reportNumber},<#t>
${expenseNumber}<#lt>
<#assign expenseNumber = expenseNumber + 1>
</#list>
<#assign reportNumber = reportNumber + 1>
</#list>'
Here is the Power Query Advanced Editor I am having issues with:
let
URLRequest = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription={
""type"":""file"",
""credentials"":{
""partnerUserID"":""username"",
""partnerUserSecret"":""password""
},
""onReceive"":{
""immediateResponse"":[""returnRandomFileName""]
},
""inputSettings"":{
""type"":""combinedReportData"",
""reportState"":""APPROVED"",
""limit"":""10"",
""filters"":{
""startDate"":""2019-01-01"",
""endDate"":""2019-10-01""
}
},
""outputSettings"":{
""fileExtension"":""csv""
},
}",
BodyRequest = "<#if addHeader == true>
Merchant,Original Amount,Category,Report number,Expense number<#lt>
</#if>
<#assign reportNumber = 1>
<#assign expenseNumber = 1>
<#list reports as report>
<#list report.transactionList as expense>
${expense.merchant},<#t>
<#-- note: expense.amount prints the original amount only -->
${expense.amount},<#t>
${expense.category},<#t>
${reportNumber},<#t>
${expenseNumber}<#lt>
<#assign expenseNumber = expenseNumber + 1>
</#list>
<#assign reportNumber = reportNumber + 1>
</#list>",
Request = Csv.Document(Web.Contents(URLRequest, [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(BodyRequest)]))
in
Request
Any assistance or guidance would be greatly appreciated!!
Thanks!
Natalie
Hi @Anonymous ,
We can try to use the Uri.EscapeDataString function to encode the body request:
let
Description = "requestJobDescription={
""type"":""file"",
""credentials"":{
""partnerUserID"":""username"",
""partnerUserSecret"":""password""
},
""onReceive"":{
""immediateResponse"":[""returnRandomFileName""]
},
""inputSettings"":{
""type"":""combinedReportData"",
""reportState"":""APPROVED"",
""limit"":""10"",
""filters"":{
""startDate"":""2019-01-01"",
""endDate"":""2019-10-01""
}
},
""outputSettings"":{
""fileExtension"":""csv""
},
}",
BodyRequest = Uri.EscapeDataString("template=
<#if addHeader == true>
Merchant,Original Amount,Category,Report number,Expense number<#lt>
</#if>
<#assign reportNumber = 1>
<#assign expenseNumber = 1>
<#list reports as report>
<#list report.transactionList as expense>
${expense.merchant},<#t>
<#-- note: expense.amount prints the original amount only -->
${expense.amount},<#t>
${expense.category},<#t>
${reportNumber},<#t>
${expenseNumber}<#lt>
<#assign expenseNumber = expenseNumber + 1>
</#list>
<#assign reportNumber = reportNumber + 1>
</#list>
"
),
RelativePathString = "/Integration-Server/ExpensifyIntegrations",
URLRequest = "https://integrations.expensify.com",
Request = Csv.Document(Web.Contents(URLRequest, [RelativePath=RelativePathString, Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(Description & BodyRequest)]))
in
Request
Best regards,
Thanks for the response @v-lid-msft ,
Unfortunately it still results in the "Response Message: No Template Submitted". I have updated my Advanced Editor to be the text for reference as this progresses.
Any other ideas?
Seems silly that this template parameter is being such a pain!
Hello,
I've been tackeling the exact same problem, any chance you eventually found a solution?
Thanks,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.