Conditional Table

[DonRoberto1985]

Hi,

I'm breaking my head on this thing 🙂

I want to see only one entry per month of a device(ResID) on the following (prioritized) condition: If all of them are compliant, then as compliant. If one of them is non-compliant then as non-compliant, If one of them is null then as null.

Thanks in advance,

Robert

[serpiva64 · Accepted solution]

Hi, 

you can obtain this result

applaying the steps of the attache file

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[DonRoberto1985]

Thanks a lot 🙂

[AlB · Accepted solution]

Hi @DonRoberto1985 

Place the following M code in a blank query to see the steps.

You might have to change

[LastComplianceMessageDesc] = ""

for

[LastComplianceMessageDesc] = null

in the #"Added Custom" step 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZK9CoNAEIRfRa424OxF91Lb5wXEIlgJRlP4/uTUKAZOdrq9m49h9qdpHCpVDRJc7p7TeOum92foX+Mc31KIzxALuDb/I2uKOovesJDsEQs1qeUzmJSmvHaxouIsVGlSJeV1pyifonZRiDVslJgUhBo1wGFFaiWHyiUiKKwruTgybE3ZHkgO8lC5IPide/sF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ResID = _t, LastComplianceMessageDesc = _t, Date = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ResID", Int64.Type}, {"LastComplianceMessageDesc", type text}, {"Date", type date}, {"Count", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sorting", each if [LastComplianceMessageDesc] = "Non-compliant" then 1 else if [LastComplianceMessageDesc] = "" then 2 else 3, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ResID", "Date"}, {{"Count", each Table.Min(_, "Sorting")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"LastComplianceMessageDesc", "Count"}, {"LastComplianceMessageDesc", "Count"})
in
    #"Expanded Count"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[serpiva64]

Please can you paste data (not images) and the required result?

[DonRoberto1985]

Data:

ResID	LastComplianceMessageDesc	Date	Count
16777828	Non-compliant	2023 1	1
16777828	Compliant	2023 1	1
16777828	null	2023 1	3
16777828	Compliant	2022 9	7
16777828	Compliant	2022 8	8
16777828	Compliant	2022 7	7
16777828	null	2022 6	3
16777828	Compliant	2022 6	5
16777828	Compliant	2022 5	3
16777828	Compliant	2022 4	3
16777828	Compliant	2022 3	3
16777828	null	2022 2	1
16777828	Compliant	2022 2	2
16777828	Compliant	2022 12	7
16777828	Compliant	2022 11	7
16777828	Compliant	2022 10	8
16777828	null	2022 1	2
16777828	Compliant	2022 1	2
16777828	Compliant	2021 6	1
16777828	null	2021 12	2
16777828	Compliant	2021 12	1
16777828	null	2021 11	2
16777828	Compliant	2021 11	1

Result:

ResID	LastComplianceMessageDesc	Date	Count
16777828	Non-compliant	2023 1	1
16777828	Compliant	2022 9	7
16777828	Compliant	2022 8	8
16777828	Compliant	2022 7	7
16777828	null	2022 6	3
16777828	Compliant	2022 5	3
16777828	Compliant	2022 4	3
16777828	Compliant	2022 3	3
16777828	null	2022 2	1
16777828	Compliant	2022 12	7
16777828	Compliant	2022 11	7
16777828	Compliant	2022 10	8
16777828	null	2022 1	2
16777828	Compliant	2021 6	1
16777828	null	2021 12	2
16777828	null	2021 11	2

Thanks 🙂

[serpiva64 · Accepted solution]

Hi, 

you can obtain this result

applaying the steps of the attache file

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !