Splitting a table based on a value in a column

[JJ2022PBI]

Hi,

I will be refreshing a large file each month that has all the sales data for a company by business area (Retail & Internet). I would like to have two different tables (one for each business area) that updates with the latest monthly data each time the main table is refreshed, ultimately having three tables (Total, Retail, Internet).

I wasn't sure of the best way of doing this, either by using the power query editor or using a union function within Dax but wrapping it with a filter for the business area. As there will be DAX formulas linking to the new tables I don't want to have to re-point them to brand new tables each time, it would be good if the new data slotted in at the bottom of the current table.

Thanks!

James

[JJ2022PBI]

Thanks @Idrissshatila, this process works.

However the issue I run into is that the report takes about 10 minutes to refresh due to the size of the tables (approx. 1 million rows). I simplified the process in my previous message but it is as below:

1) Table 1 contianing 2022 data (will not change - approx 1 million rows and 50 columns)

2) Table 2 containing 2023 data (new monthly data - 80k new rows added monthly)

Query Actions

1) The two tables are appended together with 22 as the first table and 23 as the second

2) The larger table is duplicated 2 times and filtered on the business area with one table showing 'Retail' and the other 'Internet'

On the 2022 large table that will not refresh I unselected 'Enable Load' and 'Include in report refresh' and for the 2023 data I unselected the 'Include in report refresh' button.

That means there are now three tables in my model, Total Sales, Total Retail Sales, Total Internet Sales.

Thanks!

[Idrissshatila]

Hello @JJ2022PBI ,

For having 2 different tables, duplicate the main table in power query and then filter the business area column in each table for the value you want.

regarding the last month only refresh, check the incremental refresh method https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin