Incremental Refresh

[Sfm7]

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed

I am getting this error how can I fix these so I can get incremental refresh (store last year and update last two months).

Please help

This is my code:

let
    // Function to get the token
GetToken = () =>
    let
    body = [username="viv@myspace.com",password="xxxxxxx"],
    Source = Web.Contents("https://myspace.com/vesb/api/ac/sec/account/login", 
        [
        
            Headers=[#"Content-Type"="application/json"]
        ]),
    // Convierte el contenido JSON de la respuesta
    JsonContent = Json.Document(Source),
    // Extrae el token del campo 'Token' dentro de 'Data'
    Token = JsonContent[Data][Token]
in
    Token,
    // Define the function with fixed dates

    // Define the function with fixed dates
    GetDataByFixedDateRange = () =>
    let
        // Define the start and end dates
         DateStart = Date.From(RangeStart),
         DateEnd = Date.From(RangeEnd),
        
        // Generate a list of dates between the RangeStart and RangeEnd
        dateList = List.Dates(DateStart, Duration.Days(DateEnd - DateStart) + 1, #duration(1, 0, 0, 0)),
        
        // Function to retrieve data for a single date
        GetDataForDate = (Fecha as date) =>
        let
            FechaText = Date.ToText(Fecha, "yyyy-MM-dd"),
            
            // Construct the URL using the provided date
            url = "https://myspace.com/vesb/api/public/vpos/Report/monthlySalesExt/" & FechaText,
            
            // Retrieve the CSV document from the constructed URL
            csvContent = Web.Contents(url, [Headers=[Authorization="vToken " & GetToken()]]),
            
           // Convert the CSV content to a table
            csvTable = Csv.Document(csvContent, [Delimiter=",", Columns=53, Encoding=1252, QuoteStyle=QuoteStyle.None]),
            
            // Promote the first row to headers
            promotedHeaders = Table.PromoteHeaders(csvTable, [PromoteAllScalars=true]),
            
            // Change column types as needed
            changedTypes = Table.TransformColumnTypes(promotedHeaders, {
                {"Rank", Int64.Type}, {"TransactionID", type text}, {"Base", type text}, {"Origin", type text}, {"Destination", type text}, 
                {"Flight", type text}, {"FlightDate", type datetime}, {"ShortFlightDate", type date}, {"FlightDateTime", type time}, 
                {"TransactionDate", type datetime}, {"ShortTransactionDate", type date}, {"TransactionDateTime", type time}, 
                {"EU_NonEU", type text}, {"CountryOfOrigin", type text}, {"CountryOfDestination", type text}, {"AircraftRegistration", type text}, 
                {"AircraftType", type text}, {"CrewMember", Int64.Type}, {"ProductType", type text}, {"ProductCode", type text}, 
                {"ProductName", type text}, {"Quantity", Int64.Type}, {"CostPrice", Int64.Type}, {"NetSales", type number}, {"Vat", type number}, 
                {"TotalSales", Int64.Type}, {"Cash", type text}, {"Card", type text}, {"Voucher", type text}, {"PaymentDetails", type text}, 
                {"Reason", type text}, {"Voided", type text}, {"DeviceCode", Int64.Type}, {"DeviceName", type text}, {"PaymentStatus", type text}, 
                {"BaseCurrencyPrice", Int64.Type}, {"SaleItemId", Int64.Type}, {"SaleItemStatus", Int64.Type}, {"VatRateValue", Int64.Type}, 
                {"CrewBaseCode", type text}, {"RefundReason", type text}, {"PaymentDevice", type text}, {"PaymentId", Int64.Type}, 
                {"PriceType", type text}, {"SeatNumber", type text}, {"PassengerCount", Int64.Type}, {"ScheduleActualDepartureTime", type text}, 
                {"ScheduleActualArrivalTime", type text}, {"SaleTypeName", type text}, {"PriceOverrideReason", type text}, {"DiscountType", type text}, 
                {"ScheduledFlightDate", type text}, {"ActualFlightDate", type text}
            }),
            
            // Add the date column
            withDateColumn = Table.AddColumn(changedTypes, "Fecha", each Fecha, type date)
            
        in
            withDateColumn,
        
        // Retrieve data for all dates in the list
        allData = List.Transform(dateList, each GetDataForDate(_)),
        
        // Combine all tables into a single table
        combinedTable = Table.Combine(allData),
        transformcolumn=Table.TransformColumnTypes(combinedTable,{{"Fecha", type datetime}})
        
    in
        // Return the combined table
        transformcolumn
in
    // Return the function
    GetDataByFixedDateRange

[lbendlin · Accepted solution]

Download the cold data into CSV files that you store on OneDrive. Append them in Power Query with your warm and hot partitions.

[Sfm7]

regarding the relative path I keep messing up this is my code:

let

    searchText = "2024-09-06"

    url = "https://viv.i-soms.com/vesb/api/public/vpos/Report/monthlySalesExt/"

    csvContent = Web.Contents(url, [Headers=[Authorization="vToken " & Login2()]])

    in

        Web.Contents(

            url,

            [

                RelativePath = "",

                Query = [q = searchText]

            ]

        )

Response 

Url=https://viv.i-soms.com/vesb/api/public/vpos/Report/monthlySalesExt/?q=2024-09-06 these ?q should not be ther to work

[lbendlin · Accepted solution]

let

    searchText = "2024-09-06",

    url = "https://viv.i-soms.com/vesb/api/public/vpos/Report/monthlySalesExt/",

    csvContent = Web.Contents(url, [Headers=[Authorization="vToken " & Login2()],RelativePath = searchText] )

in

    csvContent

[Sfm7]

Thank you for your response, and is there a way to speed up the query, I have 10 Million rows of data most of them are cold data (Historic from a year ago) and it doesn't change anymore the only one that changes is from 3 months prior current date and after that it doesn´t change, every time I update my query fetches all 10 million rows and takes hours, is there a more efficient way to aproach this..

Thanks for your help

[lbendlin · Accepted solution]

Download the cold data into CSV files that you store on OneDrive. Append them in Power Query with your warm and hot partitions.

[lbendlin]

// Construct the URL using the provided date
            url = "https://myspace.com/vesb/api/public/vpos/Report/monthlySalesExt/" & FechaText,

Please follow the documentation. Use RelativePath parameter. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

Note:  Incremental Refresh will not give you any performance advantage as your data dource doesn't fold.

       // Generate a list of dates between the RangeStart and RangeEnd
        dateList = List.Dates(DateStart, Duration.Days(DateEnd - DateStart) + 1, #duration(1, 0, 0, 0)),

This is incorrect. RangeEnd must be exclusive. Lose the "+1"