Splitting a table into two based on column value

[murt112]

Hi!

I'm fairly new to PowerBI and have slowly been experimenting with different functions but I've sort of hit a dead end with this. I have a master excel file that is populated with data month on month and has close to 2 years of data now. I want to load this master table to Power BI and then split it into 2 tables based on the year column. I've tried passing a parameter as a filter but that only filters the master table.

I tried creating a table by writing a DAX expression. That worked if I type the actual year in the filter function but it stopped working when I tried to pass a parameter into the filter function. If this was in SQL I could simply use the where statement to get this done. I imagine the solution is similarly easy for this one and I'm just missing something glaringly obvious.

I've attached a screenshot of a mock data set in-case my explanation wasn't clear enough.

Thanks!

[SQLMonger]

Even simpler: Use the query "Reference" feature. 

In the query panel on the left, right-click on your original import from Excel and clear the check box for "Enable Load", then right-click on it again and select "Reference". This will create a new query that references the first. Apply your year filter to the new query, rename the query and repeat as needed.

Warm regards,

Clayton

[Anonymous]

Jut what I was looking for... You are a savior!

[v-yuezhe-msft · Accepted solution]

Hi @murt112,

In Query Editor of Power BI Desktop, right click master query and then choose “Duplicate” as shown in the following screenshot.

Filter year to 2015 in the first duplicated query and filter year to 2016 in the second duplicated query.

And you can also rename the first duplicated query to 2015 and another to 2016.

Thanks,
Lydia Zhang