Refreshing a dataset with 2 conditions

[amir_mm]

Hello,

I have an idea but I'm not sure how to implement it.

Within a dataset, there is a table including a column called "Status," with distinct values of 1, 2, 3, and 4. I want to refresh the entire dataset every 30 minutes in Power BI service, where Status <>1. Also, I need the entire dataset, without any filters, to be refreshed once a day (this daily one could be scheduled through Power Automate or Azure Runbook as well).

I would greatly appreciate any ideas or suggestions for implementing this plan.

[lbendlin]

Create custom partitions based on the Status filter.  Update the partitions for 2,3 and 4 every 30 minutes (why so often?) for example with Power Automate.  Once a day, refresh partition 1 as well.

[amir_mm]

Thanks a lot!

I created 2 custom partitions with Tabular editor:

1st partition: Status<>1 with 200,000 rows (I could refresh this partition using PowerShell in Runbooks without errors)

2nd partition: Status =1 with 8 M Rows, got the below error:

System.Management.Automation.MethodInvocationException: Exception calling "ExecuteNonQuery" with "0" argument(s): "Column '<ccon>Id</ccon>' in Table '<ccon>Transactions</ccon>' contains a duplicate value '<ccon>16915320</ccon>' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

I'm confident that there are no duplicates in these columns. As a test, I disconnected all relationships between this 'Transactions' table and other tables, then the second partition refreshed successfully. But, upon adding any child table to this 'Transactions' table, the above error occurs.

It's also weird why the first partition works but the second. 

[lbendlin]

Check for trailing spaces or any other extra characters.  Check for nulls in the key column.

Make sure that value exists only in one partition.