Count missing documents

[BrittKing]

Between 2 tables I need to know all Clients that do not have a Document recorded in a specific Category.
Table 1: ‘WB-Case’ lists all ‘Clients’,
Table 2: ‘WB-Documents’ lists all ‘Documents’ recorded under each ‘Category’.
I need to get a count of ‘Clients’ that do not have a ‘Document’ recorded for each specific ‘Category’.
Note only those with a ‘Document’ are listed in Table 2

[ankit]

Hi @BrittKing - Please share the PBIX file.

Thanks
Ankit J

[pranamg]

@BrittKing

Between 2 tables I need to know all Clients that do not have a Document recorded in a specific Category.
Table 1: ‘WB-Case’ lists all ‘Clients’,
Table 2: ‘WB-Documents’ lists all ‘Documents’ recorded under each ‘Category’.
I need to get a count of ‘Clients’ that do not have a ‘Document’ recorded for each specific ‘Category’.
Note only those with a ‘Document’ are listed in Table 2

Below is one way to do it using Power Query’s M language. In this sample, we assume that:

• You already have two queries in Power Query:
– WBCase (with the column [Client])
– WBDocuments (with at least the columns [Client], [Document], and [Category])
• The goal is to count, for each distinct Category (from WBDocuments), how many Clients (from WBCase) do not have any Document recorded in that Category.

Because only clients with documents appear in the WBDocuments table (for a given Category), we can “simulate” a full (Client, Category) cross join and then perform a left join against WBDocuments. The missing entries (nulls) in the join result indicate the clients that do not have a recorded document for that Category.

Below is a complete Power Query script that does the following:

1. Get all distinct Categories from WBDocuments.
2. Create a cross join between WBCase and distinct Categories.
3. Left-join the simulated full table with WBDocuments based on [Client] and [Category].
4. Filter for rows with no matching document.
5. Group by Category and count the missing clients.

Note: Adjust the source steps as needed if your tables come from Excel, databases, etc.

---

// Begin Power Query Script
let
    // Assume these are your two queries loaded already:
    // WBCase: all clients.
    // WBDocuments: documents, each with a Client, Document, and Category.

    // For demonstration, we create sample tables:
    // (Remove or modify these steps if WBCase and WBDocuments are already loaded)
    WBCase = Table.FromRecords({
        [Client = "Client1"],
        [Client = "Client2"],
        [Client = "Client3"],
        [Client = "Client4"]
    }),
    WBDocuments = Table.FromRecords({
        [Client = "Client1", Document = "DocA", Category = "Cat1"],
        [Client = "Client2", Document = "DocB", Category = "Cat1"],
        [Client = "Client1", Document = "DocC", Category = "Cat2"],
        [Client = "Client3", Document = "DocD", Category = "Cat3"]
        // Note: Only clients with a document for a given category appear here.
    }),

    // 1. Get distinct categories from WBDocuments.
    DistinctCategories = Table.Distinct(Table.SelectColumns(WBDocuments, {"Category"})),

    // 2. Create a Cross Join between WBCase and DistinctCategories.
    //    One way to generate a cross join is to add a dummy column with a constant value to each table.    
    WBCaseWithKey = Table.AddColumn(WBCase, "DummyKey", each 1),
    CategoriesWithKey = Table.AddColumn(DistinctCategories, "DummyKey", each 1),
    CrossJoin = Table.Join(WBCaseWithKey, "DummyKey", CategoriesWithKey, "DummyKey", JoinKind.Inner),
    // Remove the dummy key column.
    CrossJoinClean = Table.RemoveColumns(CrossJoin,{"DummyKey"}),

    // 3. Left join the cross join with WBDocuments based on Client and Category.
    //    This adds a new column with the matching rows from WBDocuments.
    JoinedTable = Table.NestedJoin(
                        CrossJoinClean, 
                        {"Client", "Category"}, 
                        WBDocuments, 
                        {"Client", "Category"}, 
                        "DocRecord", 
                        JoinKind.LeftOuter
                    ),

    // 4. Flag the rows with no matching Document.
    //    We check if the nested table in DocRecord is empty.
    WithMissingFlag = Table.AddColumn(JoinedTable, "MissingDoc", each if Table.IsEmpty([DocRecord]) then true else false),
    
    // Filter only rows where MissingDoc is true (i.e. no document found for that Client-Category combination).
    MissingOnly = Table.SelectRows(WithMissingFlag, each [MissingDoc] = true),

    // 5. Group by Category and count Clients missing a document for that Category.
    GroupedResult = Table.Group(
                        MissingOnly, 
                        {"Category"}, 
                        {{"NumClientsWithoutDocument", each Table.RowCount(_), type number}}
                    ),

    // Optionally, sort the results by Category.
    SortedResult = Table.Sort(GroupedResult, {{"Category", Order.Ascending}})
in
    SortedResult

---

Explanation:

• We add a constant (“DummyKey”) to both WBCase and DistinctCategories and then join on that key. This creates a cross join (i.e. every client combined with every distinct category).

• We then use Table.NestedJoin to left-join this cross joined table with the actual WBDocuments table on both the Client and Category columns.

• The new column [DocRecord] will contain matching rows (if any) from WBDocuments. If it’s empty then the client does not have a document for that category.

• Finally, we group by Category and count the number of missing document records, which gives you the count of Clients that do not have a Document recorded for each specific Category.

You can load the resulting query into your report or further process it as needed.