Full incremental table reset/refresh

[10xape]

Hi I have added additional column to my dataflow table (incremental refresh) table that is linked to sementic model in Power BI service (also incremental refresh) and want to update it. Model is too large to do it from PB Desktop. I have tried to do full refresh of that table through TE3 but still cant see that additional column. I used Table full refresh, shoudl I select any other type of refreshing?

Thanks

Matt

[otykier]

A refresh operation does not update the schema of a table (i.e. columns and data types) - only the data in the columns that are currently specified on the table. To add the new column through TE3, you must either manually add it (right-click on the table, Create > Data column), or you can right click on the table and choose the Update table schema... option, to have TE3 attempt to update the schema automatically.

[10xape]

thanks, I have tried right click update table and get thsi error:

[otykier]

Could you share the M expression of your table? (Please blank out any sensitive information such as server names or credentials)

You may be able to work around this issue by enabled Use Analysis Services for change detection under Tools > Preferences > Schema Compare:

[10xape]

Thank you, this is teh code:

let
    // Function to enable incremental refresh
    SourceFunction = (RangeStart as nullable datetime, RangeEnd as nullable datetime) =>
    let
        // Original query steps
        Source = PowerPlatform.Dataflows(null),
        Workspaces = Source{[Id="Workspaces"]}[Data],
        #"XXXXXXXXXXXXXX" = Workspaces{[workspaceId="XXXXXXXXXXXXXX"]}[Data],
        #"XXXXXXXXXXXXXX" = #"XXXXXXXXXXXXXX"{[dataflowId="XXXXXXXXXXXXXX"]}[Data],
        cmc_cap_bridge_PQ2_ = #"XXXXXXXXXXXXXX"{[entity="cmc_cap_bridge_PQ2", version=""]}[Data],

        // Ensure 'refresh_date' is of type datetime
        ConvertedType = Table.TransformColumnTypes(cmc_cap_bridge_PQ2_, {{"refresh_date", type datetime}}),

        // Filter the data using RangeStart and RangeEnd
        FilteredRows = Table.SelectRows(
            ConvertedType,
            each [refresh_date] >= RangeStart and [refresh_date] < RangeEnd
        ),

        // Remove duplicates by 'unique_identifier'
        RemovedDuplicates = Table.Distinct(FilteredRows, {"unique_identifier"})
    in
        RemovedDuplicates,

    // Default parameter values for initial setup
    DefaultRangeStart = #datetime(2023, 1, 1, 0, 0, 0),
    DefaultRangeEnd = DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(), 0),

    // Invoking the function with default or provided parameters
    InvokedFunction =
        if RangeStart = null and RangeEnd = null then
            SourceFunction(DefaultRangeStart, DefaultRangeEnd)
        else
            SourceFunction(RangeStart, RangeEnd)
in
    InvokedFunction

I am also tryint the other method that you advised about but it still has very long refreshing time.

[otykier]

Regarding the issue about the refresh taking a long time, I noticed that you have a Table.Distinct in the M query. This should generally be avoided, as this type of operation can be incredibly slow. Better to push that upstream to your dataflow, or better yet, to the source of the dataflow.

[10xape]

I think I already did it , I sometimes repat that twice to make sure it is not duplicating cuz of folding or some other actions, thank for teh tip

[otykier]

FWIW, we have a new update out now, which solves the Unable to detect schema changes error, that you reported initially: https://docs.tabulareditor.com/te3/other/release-notes/3_19_0.html?tabs=downloads

[10xape]

Alos after scheme update I got the column and values just for one day (most recent) while in the source dataflow the value occurs accross the months, so I am not sure what is happenieng..... any assiatnce appriciated thank you! Matt

[otykier]

It's as simple as opening two instances of Tabular Editor - connect one to Power BI Desktop with your temporary model, and the other instance to the model you're currently working on.

Then - in the first instance, copy (Ctrl+C) the table you just set up in Power BI Desktop. In the other instance, select the table you just recreated in Power BI Desktop, then hit paste (Ctrl+V). You should then see the following dialog to confirm that you will override the destination table with the one you just copied:

[10xape]

oh so it is just a copying of the M code to the PB serv model, ok thanks

[10xape]

Hey Daniel could you please unfold a little bit on that subject? I looked at this docs article https://docs.tabulareditor.com/te3/tutorials/incremental-refresh/incremental-refresh-workspace-mode.html I have a problem with getting my had aroudn where the metadata is actually stored? Is that a source expression M code or I pull it from the other option/view? Could you please provide a full screen shots on how you access meetadat for the worksapce incremental refresh model in TE3? Thanks so much!

[otykier]

This thread is getting a bit unwieldy, and it's a bit unclear to me what your exact goal is. I would be happy to help, but may I ask that you post a new discussion/issue where you clearly state what exactly you are trying to achieve, what data source you are using, what you have done in Power BI, what you are trying to do with Tabular Editor 3, and the issues that you are seeing (error messages, etc.). Thank you!

[10xape]

thanks and sorry for that, I understand some of these are a beginner questions so I really appricate your patience. I did this thread yesterday that I think is going to cover it : Updating worspace incremental refresh model #1

[edhans]

Your code above will not work. The source data must be DateTime for IR to work, and the ChangeType you are doing will break folding. Get with your DBA to have the data converted to DateTime in the source. Then folding will happen and it should refresh normally.

What is happening with the long refresh is 100% of the table is being downloaded for every partition and then it discards the data that isn't needed - data outside your RangeStart/RangeEnd date ranges for each partition. So if you have 12 monthly partitions, it is downloading the entire table 12 times. It would be faster to discard IR in this case and just refresh the table one time.

[10xape]

Hey edhans, the time_lable column is not the data, it is a label for a time of the day. the date is in another column refresh_date :)

[edhans]

It doesn't matter. You are putting a Table.TransformColumnType() function in the query before the incremental refresh filter. Comment it out or move it after the IR filter and see if that has an effect.

[10xape]

you are right, thanks ! I am applying that now.

[10xape]

I cannot update the code from TE2 editor, this error is poping up, this is for duplicates removal but both amendments have teh same error

[otykier]

As mentioned in a comment above, we now have an update available which solves the issues mentioned in this thread, regarding performing the table schema update: https://docs.tabulareditor.com/te3/other/release-notes/3_19_0.html?tabs=downloads