Delete some rows completely with dependent columns in trasformation in PowerBI

[deb_power123]

Hi All,

I have a table Students with two columns in my input source data as shown  below :-

Here Building and Floor are dependent columns for eg : Building A has 4 floors Floor1 through Floor4 and so on.

I will be showing this in my table matrix so in my tranformation, I need to exclude some of the rows to obtain the expected output.

Input Data :

Building	Floor
A	Floor1
A	Floor2
A	Floor3
A	Floor4
B	Floor5
B	Floor7
B	Floor11
C	Floor15
D	Floor1
D	Floor3
E	Floor5
E	Floor8
E	Floor9
E	Floor10

Expected output :-

Building	Floor
A
A
A	Floor3
A	Floor4
B	Floor5
B
B	Floor11
C	Floor15
D	Floor1
D
E	Floor5
E	Floor8
E	Floor9
E

Finally the data after deletion or exclusion, should look like this.  :-

Building	Floor
A	Floor3
A	Floor4
B	Floor5
B	Floor11
C	Floor15
D	Floor1
E	Floor5
E	Floor8
E	Floor9

Could someone please suggest how we can achieve this through any DAX or any other method? How can I delete or exclude the unwanted rows where with dependent columns.

Kind regards

Sameer

[Ashish_Mathur]

What is the criteria for excluding rows?

[v-robertq-msft · Accepted solution]

Hi, @deb_power123 

According to your description and sample data, I think that you can just simply use the “Filter data” function in the Power Query to prevent the rows with blank value in the [Floor] column to be imported into the Power BI, like this:

And you can get what you want, like this:

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[deb_power123]

Thankyou 🙂

[amitchandak]

@deb_power123 , what is the logic to delete or remove. because 2nd table says expected output

[deb_power123]

Hi @amitchandak 

we dont want these rows so i want to remove them all so that my table matrix doesnt contain them.

It is a specific set of row which we donot want and it corresponds to the dependent column Building and Floor.

Can any DAX help in this case? Like filter dax where I want to remove all the rows say for Building A which corresponds to floor 1 and 2.I just want to remove all the rows corresponding to it.

Kind regards

Sameer