Incremental Refresh with API

Hi PedroModa ,

Of course, I can help with that. The "DATA_CRIACAO column was not found" error is almost certainly happening because the previous step in your query is producing an empty table. This occurs when your API call doesn't return any data, and the most likely culprit is a small formatting error in how you pass the parameters to the API within your custom function.

The Web.Contents function in Power Query is particular about how it receives query parameters. It expects the Query option to be a record where each field is a separate parameter, like [ParameterName1 = Value1, ParameterName2 = Value2]. Your function, however, combines everything into a single text string assigned to a parameter named parameters, which the API likely doesn't recognize.

Your current function is likely structured like this, which is causing the issue:

Query = [
    parameters = "DATAINICIO_D=" & DataInicio & ";DATAFINAL_D=" & DataFim
]

To fix this, you need to modify the function to pass the parameters as a proper record. This change ensures that the API receives two distinct parameters, DATAINICIO_D and DATAFINAL_D, which is the standard and expected format.

Here is the corrected version of your function:

(DataInicio as text, DataFim as text) =>
let
    Fonte = Json.Document(
        Web.Contents(
            "URLDAMINHAAPI",
            [
                RelativePath = "RELATIVEPATHDAMINHAAPI",
                Query = [
                    DATAINICIO_D = DataInicio,
                    DATAFINAL_D = DataFim
                ]
            ]
        )
    )
in
    Fonte

After updating your fx_fPartida function with this corrected code, you can debug the issue in your main query. In the Power Query Editor, select the Fonte step in the "Applied Steps" pane. Before the fix, you would likely see an empty list []. After the fix, you should see a List of Records. You can click on one of the records to inspect the fields and confirm that the DATA_CRIACAO column is now present. Once Fonte returns the correct data, all subsequent steps will execute without error.

Your main query will remain structurally the same, but it will now work as intended with the corrected function call.

// Main Query
let
    // Power BI automatically provides RangeStart and RangeEnd
    StartDateText = DateTime.ToText(RangeStart, "yyyy-MM-dd"),
    EndDateText = DateTime.ToText(RangeEnd, "yyyy-MM-dd"),

    // Call the corrected function
    Fonte = fx_fPartida(StartDateText, EndDateText),

    // This step will now create a table with the correct columns
    #"Convertido em Tabela" = Table.FromRecords(Fonte),

    // This step will now find the 'DATA_CRIACAO' column
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Convertido em Tabela", {
        {"CODCOLIGADA", type text},
        {"COLI-CODLOTE", type text},
        {"COLI-IDPARTIDA", type text},
        {"DATA", type datetimezone},
        {"IDPARTIDA", type text},
        {"COLI-CREDITO", type text},
        {"COLI-DEBITO", type text},
        {"DATA_CRIACAO", type datetime}
    }),

    #"DATA como Date" = Table.TransformColumnTypes(#"Tipo Alterado", {{"DATA", type date}}),

    #"Linhas Válidas" = Table.SelectRows(#"DATA como Date", each ([IDPARTIDA] <> null))
in
    #"Linhas Válidas"