cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

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

2 REPLIES 2
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

v-yuezhe-msft
Microsoft
Microsoft

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors