cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deb_power123
Helper IV
Helper IV

Delete or Remove specific rows in Transform tab of PowerBI

Hi All,

 

I have a table say Student and my input source table is as shown below :-

 

Input Source :

 

Building Floor Attendance TotalSeats
A Floor1       2     10
A Floor2       4     10
A Floor3     15     20
B Floor2       1     5
B Floor5       6    10
C Floor1       7    15
C Floor7      18    20

 

Rows to be removed :(Highlighted as Bold)

Building Floor Attendance TotalSeats
AFloor1      2   10
AFloor2      4   10
AFloor3    15    20
BFloor2      1    5
BFloor5      6    10
CFloor1      7    15
CFloor7    18    20

 

Expected Output:

Building Floor Attendance TotalSeats
A Floor1    2   10
A Floor3   15   20
B Floor5    6   10
C Floor1    7   15

 

Is there any DAX query or any other method anyone can suggest to remove/delete the selected rows ?

 

I want to display the expected output in my Table Matrix visual.

 

How can I remove the unwanted rows using tranformation in PowerBI? 

 

Please suggest.

 

Kind regards

Sameer

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@deb_power123 , you can create a flag in power query or dax and remove those

 

power query

if ( [Building] ="A" and [floor] = "Floor2" )  or ( [Building] ="B" and [floor] = "Floor2" )

   or ( [Building] ="C" and [floor] = "Floor18" ) then 0 else  1

 

filter all 0 rows in power query and save

 

In dax

if  (( [Building] ="A" && [floor] = "Floor2" )  || ( [Building] ="B" && [floor] = "Floor2" )

   || ( [Building] ="C" && [floor] = "Floor18" ) ,0 ,1)

 

filter this at visual or page level, if you create a flag in DAX



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@deb_power123 , you can create a flag in power query or dax and remove those

 

power query

if ( [Building] ="A" and [floor] = "Floor2" )  or ( [Building] ="B" and [floor] = "Floor2" )

   or ( [Building] ="C" and [floor] = "Floor18" ) then 0 else  1

 

filter all 0 rows in power query and save

 

In dax

if  (( [Building] ="A" && [floor] = "Floor2" )  || ( [Building] ="B" && [floor] = "Floor2" )

   || ( [Building] ="C" && [floor] = "Floor18" ) ,0 ,1)

 

filter this at visual or page level, if you create a flag in DAX



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

I used second approach and it worked as expected

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.