Forum Discussion

msellner_1025's avatar
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...
  • ams1's avatar
    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's avatar
    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