Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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').
Solved! Go to Solution.
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.
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
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
Output
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
Output
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.
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
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
Output
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
Output
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 😊
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 😊
@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 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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
83 | |
82 | |
67 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |