Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
murt112
New Member

Splitting a table into two based on column value

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!

 

Sample.PNG

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

Hi @murt112,

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


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

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

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
SQLMonger
Advocate II
Advocate II

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
Not applicable

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

v-yuezhe-msft
Employee
Employee

Hi @murt112,

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


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

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

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.