Forum Discussion

Fair-UL's avatar
Fair-UL
Icon for Helper II rankHelper II
6 years ago

Filter a column by a specific value in another column

Hi. I have this mock data table project Findings x 1 x 1 x 1 x 1 y 1 y 2 y 1 y 1 z 1 z 2 z 1   I want the result to be the project ID that has ...
Need Help
mussaenda's avatar
mussaenda
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Fair-UL,

 

This may not be the best solution but it achieves what you want:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUTJUitUhxKpEYRlhEYOwqlBYRshisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column1", Order.Ascending}, {"Column2", Order.Descending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Column2] = 2 then "Exclude"
else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
    #"Filled Down"