How to periodically *fully* the incremental tables of a Dataflow

[gtzanakis]

Dear all,

I have a Dataflow that includes tables configured to be refreshed incrementally. My problem is that I would like to periodically run a full refresh, in order to catch various updates and retrospective changes and fixes in my source data. Unfortunately, there does not seem to be a native option for that and neither is possible to trigger a *full* refresh via the rest API (see the relevant discussion here. 

This is a huge issue for us. Is there any reasonable and somewhat automatic workaround that one could suggest?

One thing that crossed my mind would be to change the M code for the table at this point, and add some logic for RangeStart and RangeEnd depending on the day of the month. In other words, and if you permit me some ugly custom pseudocode, I propose taking this part:

<my transformations>

#"my_table-xxxxxxxxxxxxxx-autogenerated_for_incremental_refresh" = Table.SelectRows(my_table, each DateTime.From([my_timestamp_column]) >= RangeStart and DateTime.From([my_timestamp_column]) < RangeEnd)

#"my_table-xxxxxxxxxxxxxx-autogenerated_for_incremental_refresh"

and turning it to something like:

<my transformations>

<if day of the month is 1>
# fetch all the data

< else>

#"my_table-xxxxxxxxxxxxxx-autogenerated_for_incremental_refresh" = Table.SelectRows(my_table, each DateTime.From([my_timestamp_column]) >= RangeStart and DateTime.From([my_timestamp_column]) < RangeEnd)

#"my_table-xxxxxxxxxxxxxx-autogenerated_for_incremental_refresh"

Would something like that work??

[v-xiaoyan-msft]

Hi @gtzanakis ,

Thanks for your feedback! If you would like to suggest feature improvements, you may vote the idea and comment here to improve this feature. It is the right place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,
Community Support Team _ Caitlyn

[v-jiewu-msft]

Hi @gtzanakis ,

Based on the description, try to use the following M code to perform a full refresh. The FullRefresh variable is set to the day of the month.

let
    Source = my_table,
    CurrentDateTime = DateTime.LocalNow(),
    CurrentDay = DateTime.Day(CurrentDateTime),
    FullRefresh = 1,
    #"Filtered Table" = if CurrentDay = FullRefresh then
        Source
    else
        Table.SelectRows(Source, each DateTime.From([my_timestamp_column]) >= RangeStart and DateTime.From([my_timestamp_column]) < RangeEnd)
in
    #"Filtered Table"

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gtzanakis]

Thank you for the reply, Wu.

Your code makes sense, but I can't get it to work. The reason is that it seems that PowerBI changes the code automatically due to the incremental setting and filters the Filtered table.

So, I open the advanced editor and I use the following code:

let
    Source = GoogleBigQuery.Database([BillingProject = null, UseStorageApi = null, ConnectionTimeout = null, CommandTimeout = null]),
    bigquery = Source{[Name = "bigquery-xxxxxx"]}[Data],
    dataset = bigquery{[Name = "xxxxxxxxxxxxxxxxxx", Kind = "Schema"]}[Data],
    my_table = dataset{[Name = "xxxxxxxxxxxxxxxx", Kind = "Table"]}[Data],

    table_renamed_columns = Table.TransformColumnNames(
        my_table,
        each Text.Replace(_, "_", if Text.Start(_, 1) = Text.Upper(Text.Start(_, 1)) then " " else "_")
    ),

    CurrentDateTime = DateTime.LocalNow(),
    CurrentDay = Date.Day(CurrentDateTime),
    FullRefresh = 18,
    
    #"Filtered Table" = if CurrentDay = FullRefresh then
            table_renamed_columns
        else
            Table.SelectRows(table_renamed_columns, each DateTime.From([reservation_timestamp]) >= RangeStart and DateTime.From([reservation_timestamp]) < RangeEnd)
in
    #"Filtered Table"

This is your code, except that a small fix (Datetime.Day -> Date.Day) and changed the FullRefresh to 18 which is today's day of the month, in order to test the code.

After saving this code, and after the initial full refresh, all subsequent refreshes only fetch the incremental data and not the full table. When I open again the code in the advanced editor, I see that PowerBI has changed the code to:

let
    Source = GoogleBigQuery.Database([BillingProject = null, UseStorageApi = null, ConnectionTimeout = null, CommandTimeout = null]),
    bigquery = Source{[Name = "bigquery-xxxxxx"]}[Data],
    dataset = bigquery{[Name = "xxxxxxxxxxxxxxxxxx", Kind = "Schema"]}[Data],
    my_table = dataset{[Name = "xxxxxxxxxxxxxxxx", Kind = "Table"]}[Data],

    table_renamed_columns = Table.TransformColumnNames(
        my_table,
        each Text.Replace(_, "_", if Text.Start(_, 1) = Text.Upper(Text.Start(_, 1)) then " " else "_")
    ),

    CurrentDateTime = DateTime.LocalNow(),
    CurrentDay = Date.Day(CurrentDateTime),
    FullRefresh = 18,
    #"Filtered Table" = if CurrentDay = FullRefresh 
        then table_renamed_columns
        else Table.SelectRows(table_renamed_columns, each DateTime.From([reservation_timestamp]) >= RangeStart and DateTime.From([reservation_timestamp]) < RangeEnd),
    
    
    #"xxxxxxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-autogenerated_for_incremental_refresh" = Table.SelectRows(#"Filtered Table", each DateTime.From([reservation_timestamp]) >= RangeStart and DateTime.From([reservation_timestamp]) < RangeEnd)
in
    #"playground_trips-7265736572766174696F6E5F74696D657374616D70-autogenerated_for_incremental_refresh"

Is there any way I could overcome this?

Thanks again

[v-jiewu-msft]

Hi @gtzanakis ,

Based on the description, incremental refresh settings change the code. Maybe there's no other way to change the incremental refresh policy.

Try using Tabular Object model to control refresh.

Viewing the following documents to learn more information.

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

Incremental Refresh | Tabular Editor Documentation

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gtzanakis · Accepted solution]

Hey Wu,

Thank you for your reply. Unfortunately "applyRefreshPolicy" is an option that only applies to Semantic Models and not Dataflows.

- https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-datase  (see applyRefreshPolicy in Parameters)

- https://learn.microsoft.com/en-us/rest/api/power-bi/dataflows/refresh-dataflow (no applyRefreshPolicy here)

This has been discussed here as well.

I think I'm losing hope about this being doable, huge miss on MS's part.. But thank you for your  help and time!