Forum Discussion

WulffJoergen's avatar
WulffJoergen
Icon for Helper I rankHelper I
1 year ago
Solved

Scheduled refresh of a RestAPI (Post request)

Hi all, 
In Power BI desktop I have PBIX file, that is working fine and I can publish the file to our workspace. 
The file is extracting data as a RestAPI connection

But when I want to create a scheduled refresh it seems like it is not working.

I then have created a dataflow, and as long as I only try to get data with a GET request it seems like I can create a dataflow, which will update on scheduled refresh hours.

But one table has to be extracted with a POST request, which is possible in Power Query window of the dataflow, but not to include in the scheduled refresh.

Does anybody have a solution for that?     (query code included below)

let
    // Function to convert a datetime to Unix timestamp (seconds since 1 Jan 1970)
    ConvertToUnixTime = (inputDate as datetime) =>
    let
        unixEpochStart = #datetime(1970, 1, 1, 0, 0, 0),
        duration = Duration.From(inputDate - unixEpochStart),

        // Convert the duration to total seconds
        totalSeconds = Duration.Days(duration) * 86400 + Duration.Hours(duration) * 3600 + Duration.Minutes(duration) * 60 + Duration.Seconds(duration)
    in
        totalSeconds,

    // Define input parameters for startdate and enddate
    startDate = #datetime(2024, 1, 1, 0, 0, 0),  // Input your start date here
    endDate = #datetime(2024, 1, 31, 23, 59, 59), // Input your end date here

    // Convert start and end dates to Unix timestamps
    fromUnixTime = ConvertToUnixTime(startDate),
    toUnixTime = ConvertToUnixTime(endDate),

    // Function to get data with a given offset
    GetDataWithOffset = (fromUnixTime as number, toUnixTime as number, offsetValue as number) =>
    let
        // Define body with the Unix timestamps and offset
        body = "from=" & Text.From(fromUnixTime) & "&to=" & Text.From(toUnixTime) & "&offset=" & Text.From(offsetValue), // Body as URL-encoded parameters

        // Define headers
        headers = [
            firmaid = FirmaId, 
            token = Token, 
            Accept = accept,
            #"Content-Type" = contentType // Content-Type must be x-www-form-urlencoded
        ],

        // Try to send POST request
        response = try
            Json.Document(Web.Contents("https://api.onlinepos.dk/api/exportKoncern", [
                Headers = headers,
                Content = Text.ToBinary(body)  // Send body as URL-encoded data
            ]))
        otherwise
            let
                errorMessage = try Web.Contents("https://api.onlinepos.dk/api/exportKoncern", [Headers = headers, Content = Text.ToBinary(body)]) otherwise null,
                decodedMessage = if errorMessage <> null then Text.FromBinary(errorMessage) else "No response"
            in
                "Request error: " & decodedMessage,

        // Return the result
        result = response,
        sales = result[sales],
        #"Converted to Table" = Table.FromList(sales, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        #"Converted to Table",

    // Get data in 10 iterations (looping with offset)
    HentMånedensSalgsordrer = List.Transform({0..11}, each GetDataWithOffset(fromUnixTime, toUnixTime, _ * 1000)),
    
    // Combine all data into one table
    KonverterListeTilTabel = Table.FromList(HentMånedensSalgsordrer, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    KonverterTilSalgslinjer = Table.ExpandTableColumn(KonverterListeTilTabel, "Column1", {"Column1"}, {"Salgslinjer"}),
    HentLinjerne = Table.ExpandRecordColumn(KonverterTilSalgslinjer, "Salgslinjer", {"line"}, {"line"}),
    UdvidTabelTilAtViseAllePosterUdvalgtekolonner = Table.ExpandRecordColumn(HentLinjerne, "line", {"firma_id", "chk", "bo_chk", "date", "time", "product_id", "product", "product_group_id", "product_group", "MasterID", "amount", "price", "payment_type", "clerk", "table", "discount"}, {"firma_id", "chk", "bo_chk", "date", "time", "product_id", "product", "product_group_id", "product_group", "MasterID", "amount", "price", "payment_type", "clerk", "table", "discount"}),

    // Change column types for data
    SkiftDataTypeAlleKolonner = Table.TransformColumnTypes(UdvidTabelTilAtViseAllePosterUdvalgtekolonner,{{"date", type date}, {"price", type number}, {"clerk", Int64.Type}, {"firma_id", Int64.Type}, {"chk", Int64.Type}, {"bo_chk", Int64.Type}, {"time", type time}, {"product_id", Int64.Type}, {"product", type text}, {"product_group_id", Int64.Type}, {"product_group", type text}, {"MasterID", Int64.Type}, {"amount", type number}, {"payment_type", type text}, {"table", Int64.Type}, {"discount", type number}})
in
    SkiftDataTypeAlleKolonner

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi WulffJoergen,

     

    Power Automate falls under the Power Platform community, so it’s best to raise the issue in the Power Platform community

     

    You can visit the Power Platform Community Forum here:
    https://community.powerplatform.com

     

    Regards,

    Vinay Pabbu

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi WulffJoergen,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

     

    Power BI's native service only supports scheduled refresh for dataflows using GET requests. POST requests are not supported for scheduled refresh directly in Power BI.

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

     

    Regards,

    Vinay Pabbu

     

     

     

    • WulffJoergen's avatar
      WulffJoergen
      Icon for Helper I rankHelper I

      Hi Vinay,

      Do you know if there is any workarounds for that? Better solutions as SQL database as a way to grasp data and then use that as source for instance?

      Regards 
      Jørgen