Sort Active Directory users by group

You could do something like:

Use Power Query to import your CSV data, and index by Group:

let
    Source = Csv.Document(File.Contents("C:\temp\powershell.csv"),[Delimiter=",", QuoteStyle=QuoteStyle.Csv]),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(Source, {{"Column2", each Text.BetweenDelimiters(_, """", """"), type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Text Between Delimiters", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", type text}, {"member", type text}, {" enabled", type logical}}),
    #"Index Partitions" = Table.Group(#"Changed Type", {"Group"}, {{"Partition", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Index Partitions", "Partition", {"member", " enabled", "Index"}, {"member", " enabled", "Index"})
in
    #"Expanded Partition"

Load this query to the Data Model. Add a couple of measures:

Member Name:=IF ( 
    HASONEVALUE ( powershell[member] ),
    VALUES ( powershell[member] ),
    BLANK()
)

Member Enabled:=IF ( 
    HASONEVALUE ( powershell[enabled] ),
    VALUES ( powershell[enabled] ),
    BLANK()
)

Now you can use CUBEVALUE formulae to display the names:

Formula:

=CUBEVALUE("ThisWorkbookDataModel",

CUBEMEMBER("ThisWorkbookDataModel","[powershell].[Index].&["&ROW()-1&"]"),

CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Member Name]"),

A$1)

Capture.png


Finally, use a CUBEVALUE formula in conditional formatting, and format to suit:

Formula:

=NOT(CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Member Enabled]"),CUBEMEMBER("ThisWorkbookDataModel","[powershell].[Index].&["&ROW()-1&"]"),A$1))

See https://excel.solutions/xlf_1269773/ for a worked example file.