Forum Discussion

Keks's avatar
Keks
Frequent Visitor
2 years ago
Solved

Delete rows only if

Hi everybody,    Would like to find a way to flag rows when : - sequence is unique in the table  or - if sequence is not unique, then column description must be "Changed Qty'   Can someo...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Keks Try:

    Measure = 
    VAR __Seq = MAX('Table'[Sequence])
    VAR __Result = 
        SWITCH(TRUE(),
            MAX('Table'[Description]) = "Changed Qty", 1,
            COUNTROWS(FILTER(ALL('Table'), [Sequence] = __Seq)) = 1, 1,
            0
        )
RETURN
    __Result
Keks's avatar
Keks
Frequent Visitor
2 years ago

Hi Greg_Deckler ,

Finally, I would like to delete rows (they are useless) in the data instead of flagguing it by a measure.

How can I do that ? 

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

Keks For that you would need to use Power Query. Updated PBIX is attached.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctQFQiUdJb/8kozMvHQgyzGoxBBIGRooxeog5J0zEvPSU1MUAksqQWqKwGpMoEqc0I0oKjFCNsJJ14kIeQwrjCBWxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sequence = _t, Description = _t, Article = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sequence", type text}, {"Description", type text}, {"Qty", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Sequence"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Columns", each _, type table [Sequence=nullable text, Description=nullable text, Article=nullable text, Qty=nullable number]}}),
    #"Expanded Columns" = Table.ExpandTableColumn(#"Grouped Rows", "Columns", {"Description", "Article", "Qty"}, {"Description", "Article", "Qty"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Columns", "Keep", each if [Count] = 1 then 1 else if [Description] = "Changed Qty" then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count", "Keep"})
in
    #"Removed Columns"