Forum Discussion

Helper I

3 years ago

Solved

Power Query API with BodyRequest

Hello! I am trying to move a functioning API from Postman into Power Query. The issue is I do not know how to convert the x-www-form-urlencoded Body value to a Power Query accepted BodyRequest. Th...

Need Help

ams1

3 years ago

Ah, yes, the problem is that if we put "template=" inside the BodyRequest step it will go into Uri.EscapeDataString and so it will get escaped aka be converted to "template%3D" -> we need the "=" to remain as it is in the POST body so that expensify api can detect the "template" parameter.

Long story short, below worked on my machine:

let
    Description = "requestJobDescription={
""type"":""file"",
""credentials"":{
""partnerUserID"":""..."",
""partnerUserSecret"":""...""
},
""onReceive"":{
""immediateResponse"":[""returnRandomFileName""]
},
""inputSettings"":{
""type"":""combinedReportData"",
""filters"":{
""startDate"":""2023-01-01""
}
},
""outputSettings"":{
""fileExtension"":""csv""
},
}",
    BodyRequest = Uri.EscapeDataString(
        // NOTE there is NO more "template=" at the beginning of below string! ------- (!)
        "<#list reports as report>
    ${report.reportName},<#t>
    ${report.reportID},<#t>
    ${report.accountEmail}<#lt>
</#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 & "&template=" & BodyRequest)
                //                                    ^^^^^^^^^^^^ adding ampersand and template= here so it doesn't get escaped
            ]
        )
    )
in
    Request

Tadaaa 😊

Please mark this as answer if it works for you.

P.S.: I solved it using the strategy I mentioned before: PowerQuery request body has to be ~identical with curl request body - you see below they weren't as "=" got escaped to "%3D"

msellner_1025
3 years ago
Hi ams1 ,

I believe I am starting to make progress on this, but unfortunately this suggestion did not work. It does seem to accept the template now so that's great news! However, the template cannot be processed (see error message below). The PowerQuery request body is identical with the curl request body so I don't understand what the issue could be. I am still running this out of Postman multiple times a day so I know that it works outside of PowerQuery. Even if I copy the exact request body from an example in the Expensify API reference, I still get the same error.

Is there anything else that could be causing this not to work in PowerQuery?

Thank you,
Mikail

ams1

Responsive Resident

3 years ago

Glad to hear we are making progress 😊.

Can you please try with the template I provided and tell us if using my simple template works?

The template I used to test should work also with your account (I'm not a big expensify expert).

If my template works with your account (or a really simple template), then we can isolate the problem to your the template.

If using a simple template doesn't work, then please post again the final powerquery you're using.

Hopefully we put this issue to rest.

msellner_1025

Helper I

3 years ago

Hi ams1 ,

I ran a simple version of the template and it finally worked! I went through my original template and isolated the issue. I had an else statement set to null like postedDate = "" and it worked as expected when I updated it to postedDate = """".

You have been so helpful! I really appreciate it. I have accepted your great solution.

Thank you!
Mikail

ams1
Responsive Resident
3 years ago
msellner_1025 -> great news!

P.S.: I don't think you marked as answer the correct reply 😀 -> please double check so that this rests as "answered".

P.P.S: I really like expensify 😂
rehvi1108
New Member
2 years ago
Hi all
I got this one to work -> if additional fields needs to added -> you can add then once
this flow creates a cvs document and the later on, extracts it from the same template
you need to enter the username and password 2 places in the code

I used many different inputs other places and combines the inputs into this one

let
Description_F = "requestJobDescription={
""type"":""file"",
""credentials"":{
""partnerUserID"":""XXXXXX"",
""partnerUserSecret"":""XXXXXX""
},
""onReceive"":{
""immediateResponse"":[""returnRandomFileName""]
},
""inputSettings"":{
""type"":""combinedReportData"",
""reportState"":""OPEN,SUBMITTED,APPROVED,REIMBURSED,ARCHIVED"",
""filters"":{
""startDate"":""2022-01-01""
}
},
""outputSettings"":{
""fileExtension"":""csv""
},
}",
BodyRequest_F = Uri.EscapeDataString(
// NOTE there is NO more "template=" at the beginning of below string! ------- (!)
"
<#assign expenseNumber = 1>
<#list reports as report>
<#list report.transactionList as expense>
${report.reportName}, <#t>
${report.reportID}, <#t>
${report.submitted},<#t>
${report.status},<#t>
${report.policyName},<#t>
${report.manager.fullName},<#t>
${report.approved},<#t>
${report.created},<#t>
${report.submitter.fullName},<#t>
${report.approvers},<#t>
${report.employeeCustomField1},<#t>
${report.employeeCustomField2},<#t>
${report.accountEmail}, <#t>
${expense.transactionID},<#t>
${expense.amount}, <#t>
${expense.created},<#t>
${expense.comment},<#t>
${expense.currency},<#t>
${expense.type},<#t>
${expense.taxName},<#t>
${expense.taxRate},<#t>
${expense.taxRateName},<#t>
${expense.taxCode},<#t>
${expense.tag},<#t>
${expense.convertedAmount},<#t>
${expense.currencyConversionRate},<#t>
${expense.inserted},<#t>
${expense.receiptObject.url},<#t>
${expense.modifiedAmount},<#t>
${expense.modifiedCreated},<#t>
${expense.modifiedMCC},<#t>
${expense.modifiedMerchant},<#t>
${expense.merchant},<#t>
${expense.category} <#t>
</#list>
</#list>
"
),
RelativePathString_F = "/Integration-Server/ExpensifyIntegrations",
URLRequest_F = "https://integrations.expensify.com",
Request_F = Csv.Document(
Web.Contents(
URLRequest_F,
[
RelativePath = RelativePathString_F,
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(Description_F & "&template=" & BodyRequest_F)
// ^^^^^^^^^^^^ adding ampersand and template= here so it doesn't get escaped
]
)
),
Column2_F = Request_F{0}[Column1] ,

Description = "requestJobDescription={
""type"":""download"",
""credentials"":{
""partnerUserID"":""XXXXXXX"",
""partnerUserSecret"":""XXXXXXX""
},
""fileName"": "&Column2_F&",
""fileSystem"": ""integrationServer""
}",
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 & "&template=" & BodyRequest_F)
// ^^^^^^^^^^^^ adding ampersand and template= here so it doesn't get escaped
]
)
),
#"Renamed Columns" = Table.RenameColumns(Request,{{"Column1", "report.reportName"}, {"Column2", "report.reportID"}, {"Column3", "report.submitted"}, {"Column4", "report.status"}, {"Column5", "report.policyName"}, {"Column6", "report.manager.fullName"}, {"Column7", "report.approved"}, {"Column8", "report.created"}, {"Column9", "report.submitter.fullName"}, {"Column10", "report.approvers"}, {"Column11", "report.employeeCustomField1"}, {"Column12", "report.employeeCustomField2"}, {"Column13", "report.accountEmail"}, {"Column14", "expense.transactionID"}, {"Column15", "expense.amount"}, {"Column16", "expense.created"}, {"Column17", "expense.comment"}, {"Column18", "expense.currency"}, {"Column19", "expense.type"}, {"Column20", "expense.taxName"}, {"Column21", "expense.taxRate"}, {"Column22", "expense.taxRateName"}, {"Column23", "expense.taxCode"}, {"Column24", "expense.tag"}, {"Column25", "expense.convertedAmount"}, {"Column26", "expense.currencyConversionRate"}, {"Column27", "expense.inserted"}, {"Column28", "expense.receiptObject.url"}, {"Column29", "expense.modifiedAmount"}, {"Column30", "expense.modifiedCreated"}, {"Column31", "expense.modifiedMCC"}, {"Column32", "expense.modifiedMerchant"}, {"Column33", "expense.merchant"},{"Column34", "expense.category"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"report.submitted", type datetime}, {"report.approved", type datetime}, {"report.created", type datetime}, {"expense.created", type datetime}, {"expense.inserted", type datetime}, {"expense.modifiedCreated", type date}, {"expense.amount", type number}}),
#"Divided Column" = Table.TransformColumns(#"Changed Type", {{"expense.amount", each _ / 100, type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Divided Column",{{"expense.convertedAmount", type number}}),
#"Divided Column1" = Table.TransformColumns(#"Changed Type1", {{"expense.convertedAmount", each _ / 100, type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Divided Column1",{{"expense.modifiedAmount", type number}}),
#"Divided Column2" = Table.TransformColumns(#"Changed Type2", {{"expense.modifiedAmount", each _ / 100, type number}})
in
#"Divided Column2"

Forum Discussion

Power Query API with BodyRequest

Related content

Power Query dates

Data transform using power query

ayuda en power query

Power Query

Power BI downloaded PBIX does not contain Power Query queries