Expensify.org/hunger SNAP campaign status 


The number of families signing up for reimbursement has vastly outnumbered the limited funds available. Though we’re unable to provide a specific timeline on when your report will be reimbursed, we’re doing our best to drive donations and keep our fund moving forward. Rest assured, we’ll review every report that’s submitted per our instructions. 


Thanks for your patience! 

Does Expensify integrate with Microsoft Power Query?

ncandersonncanderson Expensify Customer Posts: 2

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 Expensify Success Coach - Admin Posts: 490 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 Expensify Customer Posts: 2
    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 Expensify Success Coach - Admin Posts: 46 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.