Tabular editor 2 and incremental/full refresh

[amir_mm]

Hello,

Below is part of the PowerShell script that I'm using in Azure Runbooks to refresh my PBI dataset. I included "only one table" in this script for refreshing because this is a huge table with over 10M rows, and it fails to refresh due to reaching the premium capacity memory threshold.

I tried using Tabular Editor 2 to partition the table, intending to divide it into 2-3 parts and refresh each partition separately to avoid the memory limit error.

However, after partitioning, the refresh just keeps running and never completes (both in PBI service and powershell )

Do you have any ideas on how I can make it work? Should I change or update the script?

I want the whole table to be refreshed but in multiple partitions to avoid the memory limit issue.

$Query ="{

  ""refresh"": {

    ""type"": ""full"",

    ""objects"": [

      {

        ""database"": ""My_Dataset"",

        ""table"": ""Orders""

      }

    ]

  }

}"

[amir_mm]

The table has 9M rows. I created 4 partitions in Tabular editor 2 (for 2024, 2023, 2022, 2021) using RangeStart and Range End. But when I refresh Power BI service, it only refresh the last partition (2024) and the other years won't be refreshed. For those years I used PowerShell in Azure Runbooks (with query below) and I could refresh 2023 but I cannot refresh 2022 and 2021 because of Power BI memory capacity limit. I need to be able to refresh all partitions sequentially to avoid memory capacity error. 

Any idea is really appreciated. 

$Query ="{

  ""refresh"": {

    ""type"": ""automatic"",

    ""objects"": [

      {

        ""database"": ""My_Dataset"",

        ""table"": ""Orders"",

        ""partition"": ""2023""

      }

    ]

  }

}"

[tharunkumarRTK]

@amir_mm I think the objects section should also include the partition name.

[Anonymous]

Hi  @amir_mm ,

You can try the following:

Use incremental refresh in Power BI Desktop.

Large semantic models in Power BI Premium - Power BI | Microsoft Learn

Use SSMS to refresh partitions individually.

Avoid the full refresh with Incremental Refresh in Power BI (Premium) - YouTube

The choice is to use a hybrid table, which is a table with one or more Import partitions and a DirectQuery partition.

https://sqlserverbi.blog/2021/12/27/hybrid-tables-incremental-refresh-and-table-partitioning-in-powe...

Verified that the partitions are correctly configured in Table Editor 2 and tried to check for specific partitions in the script.

"partition": "YourPartitionName"

Best Regards,

Liu Yang

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