Forum Discussion

PowerBI123456's avatar
PowerBI123456
Post Partisan
6 years ago

Checking if right code was deleted

Hi, need some help and hopefully this makes sense. I have 2 different tables below. The first table is the data. The second table tells you the combination of codes that cannot exist together for the...
Need Help
mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

I'm assuming there is some DateTime column or something to differentiate which Code came first (one sequence is correct, the other incorrect).  If so, you can use that column.  If not, I made an Index column in the query editor on your table and was able to detect the missed deletion with this column expression.  No relationship to the other table required.

 

Missed Deletion =
VAR __index =
    MIN ( Codes[Index] )
VAR __badcode =
    CALCULATE (
        MIN ( BadCombos[Column2] ),
        BadCombos[Column1] = EARLIER ( Codes[Data] )
    )
RETURN
    IF (
        NOT ( ISBLANK ( __badcode ) ),
        IF (
            CALCULATE (
                COUNTROWS ( Codes ),
                ALLEXCEPT ( Codes, Codes[ID] ),
                Codes[Index] > __index,
                Codes[Data] = __badcode
            ) > 0,
            "Missed Deletion"
        )
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

PowerBI123456's avatar
PowerBI123456
Post Partisan
6 years ago

mahoneypat Would you be able to provide a sample file so I can take a look?

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    I didn't make the index column with DAX.  I did it in the query editor using the button on the Add Column tab.  Here is the M code, but it is just simple index column addition.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLOT0k1BNL+RZnpmXmJOUCmEQgoxeqgKnBJzUktSQUyUGSMULUagwBYgRN2s01AAEMBqtlO2M02BAGl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Item = _t, Operation = _t, Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Item", type text}, {"Operation", type text}, {"Data", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • PowerBI123456's avatar
    PowerBI123456
    Post Partisan
    6 years ago

    mahoneypat  I want to see the index column you created. 

  • PowerBI123456's avatar
    PowerBI123456
    Post Partisan
    6 years ago

    mahoneypat  Hi - sorry for the delayed response, but is your code taking into the account the operation? For example, I want to look at deleted.