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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
NadaFathy193
Frequent Visitor

remove all rows with same id when a condition applies.

Hi, i have multiple lines with same 'Problem Id' and i need to remove all lines related to this 'Problem Id' when this condition applies ('Request Type' = 'Infrastructure Chang').

 

NadaFathy193_0-1712664106516.png

 

2 ACCEPTED SOLUTIONS
MOVC
Helper II
Helper II

You want to create a conditional column where your request type is Infrastructure then 1 else 0.

 

Afterwards, duplicate this whole table and then group by on Problem ID with a Max aggregation on your conditional column  to give a unique list of Problem IDs with 1 when they have infra and 0 if they never do.

 

You can then merge this back into the original table joining on Problem ID then filter out the 1's.

View solution in original post

v-xinruzhu-msft
Community Support
Community Support

Hi ,

Thanks for the solution @MOVC  and @Anonymous  provided,your answers are greate and i want to offer some information for user to refer to.

hello @NadaFathy193 , based on  your description, you can refer to the following sample.

1.Sample data 

vxinruzhumsft_0-1712731508466.png

 

You can create a calculated column 

Flag =
VAR A =
    CALCULATETABLE (
        VALUES ( 'Table'[Problem ID] ),
        'Table'[Request Type] = "Infrastructure change"
    )
RETURN
    IF ( NOT ( [Problem ID] IN A ), 1, 0 )

Then filter the flag column ,set it equal to 1

vxinruzhumsft_1-1712731604875.png

Output

vxinruzhumsft_2-1712731616273.png

2.You can also create a custom column in power query.

let a=Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Problem ID]=[Problem ID] and x[Request Type]="Infrastructure Change"))
in if a=0 then 1 else 0

Then filter column when it equal to 1

vxinruzhumsft_3-1712731850793.png

Output

vxinruzhumsft_4-1712731860404.png

Here is the whole M code. you can creat a blank query and put it to advanced editor in power query to refer to.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXLOz0vLTC8tSizJzM9T8CxJzVWK1YFIeeYlZ6ak5pXgFsChOQm/FIoxOATSihKLS4pKk0tKi1IVnDMS89JTwbLJFMvCrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Problem ID" = _t, #"Request Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Problem ID", type text}, {"Request Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Problem ID]=[Problem ID] and x[Request Type]="Infrastructure Change"))
in if a=0 then 1 else 0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Custom] = 1))
in
    #"Filtered Rows"

Best Regards!

Yolo Zhu

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

8 REPLIES 8
v-xinruzhu-msft
Community Support
Community Support

Hi ,

Thanks for the solution @MOVC  and @Anonymous  provided,your answers are greate and i want to offer some information for user to refer to.

hello @NadaFathy193 , based on  your description, you can refer to the following sample.

1.Sample data 

vxinruzhumsft_0-1712731508466.png

 

You can create a calculated column 

Flag =
VAR A =
    CALCULATETABLE (
        VALUES ( 'Table'[Problem ID] ),
        'Table'[Request Type] = "Infrastructure change"
    )
RETURN
    IF ( NOT ( [Problem ID] IN A ), 1, 0 )

Then filter the flag column ,set it equal to 1

vxinruzhumsft_1-1712731604875.png

Output

vxinruzhumsft_2-1712731616273.png

2.You can also create a custom column in power query.

let a=Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Problem ID]=[Problem ID] and x[Request Type]="Infrastructure Change"))
in if a=0 then 1 else 0

Then filter column when it equal to 1

vxinruzhumsft_3-1712731850793.png

Output

vxinruzhumsft_4-1712731860404.png

Here is the whole M code. you can creat a blank query and put it to advanced editor in power query to refer to.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXLOz0vLTC8tSizJzM9T8CxJzVWK1YFIeeYlZ6ak5pXgFsChOQm/FIoxOATSihKLS4pKk0tKi1IVnDMS89JTwbLJFMvCrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Problem ID" = _t, #"Request Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Problem ID", type text}, {"Request Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Problem ID]=[Problem ID] and x[Request Type]="Infrastructure Change"))
in if a=0 then 1 else 0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Custom] = 1))
in
    #"Filtered Rows"

Best Regards!

Yolo Zhu

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

 

 

 

thank you so much 😊

MOVC
Helper II
Helper II

You want to create a conditional column where your request type is Infrastructure then 1 else 0.

 

Afterwards, duplicate this whole table and then group by on Problem ID with a Max aggregation on your conditional column  to give a unique list of Problem IDs with 1 when they have infra and 0 if they never do.

 

You can then merge this back into the original table joining on Problem ID then filter out the 1's.

thank you so much 😊

Anonymous
Not applicable

@NadaFathy193  Do you want to just remove all rows with the Request Type = Infrastructure Chang? If not, how can you identify the dynamic Problem IDs? Do these IDs share anything in common?

 

i need to filter out all Problem Ids which have (Request type = Infrastructure change).
for example if i have the below Problem id, i need to remove all lines not just the line have 'Infrastructure change'

 

NadaFathy193_0-1712673020774.png

 

 

Anonymous
Not applicable

@NadaFathy193  You could create a conditional column in the query editor; When Problem ID = ...7140 and request Type = Infrastructure Chang, 0, else 1, then filter the table on 1s.

 

But the Problem Id is not constant value, it can be changed so i need to make it dynamic.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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