Does Expensify integrate with Microsoft Power Query?

ncandersonncanderson Posts: 2Expensify Customer

Microsoft Power Query (querying tool in Excel and Power BI) has the ability to make connections to data sources with API. I have been able to make connections to other API sources with a JSON output, however have been having issues getting it to work with Expensify. Have you had any customers successfully do this, do you know if this is possible?  


Thanks!


Natalie

Answers

  • Nicole TrepanierNicole Trepanier Posts: 784Expensify Success Coach - Admin Expensify Team

    Hi @ncanderson! I haven't used the program but I haven't heard of any issues with it. Can you tell me a bit more about the issues you are running into?

  • ncandersonncanderson Posts: 2Expensify Customer
    So I have spent quite a bit of time on this and have made some good progress. I can get successful responses when using Postman, however still having some issues in Power Query. I can get a successful response in Power Query when: requestJobDescription={"type":"reconciliation", and can successfully complete the downloader process to get the data. 

    The issue I am having is with the template parameter, when submitting a "reconciliation" it doesn't seem to be required as there must be a default set-up. But when submitting a "file" there isn't a default, so the template parameter is required and I get a "{responseMessage:No Template Submitted  response Code:410}".

    Below is the Power Query M formula language that I am using for the "File" request that is producing the "No Template Submitted". Is there any way to see why it is producing that response? Is it formatting? Encoding? ect….? Any help is appreciated!!


    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",

    Request = Csv.Document(Web.Contents(URLRequest, [RelativePath=RelativePathString, Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(Description & BodyRequest )]))

    in

    Request






  • Kirk BarrettKirk Barrett Posts: 40Expensify Team Expensify Team
    Unfortunately, Power Query isn't something that we can provide support for from our end.  Sorry about that!  I don't know if this will help, but when researching this I did come across this Power Query guide that I thought might be useful for you. 

    https://docs.microsoft.com/en-us/power-query/

    If you have any other questions regarding Expensify please let us know!  
Sign In or Register to comment.