Create Gap Analysis Table From Two Tables - Raw Data and Ideal State

Hi tbdbitl08 ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.

We really apologies for the inconvenience, after reviewing the information, I understand that managing 100 clients and 500 products with weekly and quarterly CSV updates makes individual client tables impractical. Here’s a simple, scalable solution:

• Load Data: Import the Client Table and Ideal State Table CSVs into Power BI.
• Cross-Join: In Power Query, create a unique client list from the Client Table and cross-join it with the Ideal State Table to get all expected Org-Product-Category combinations.
• Find Missing Products: Use a Left Anti join in Power Query between the cross-joined table and Client Table to identify missing products per client in a single table, matching your Example Output.
• Automate: Link CSVs to Power BI data sources and set up a weekly scheduled refresh in Power BI Service (use a gateway for on-premises files or OneDrive for cloud).
• Visualize: Build a matrix visual with Org as rows and Product as values, or use paginated reports (if Premium) to group by Org.
  
  If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

Hello There - and thank you for the help in getting this over the finish line! What I ended up doing after Importing both datasets (CSVs) into PowerBI, then Utilizing Power Query to create a new table and make a unique client list. Then Loaded in the Products. Then, Made a CrossJoin in order to create a new table of all clients and product combinations, then a Match to see if it matched to the original Client table with proudcts listed, thus telling me Yes or No if there was  a match and setting filters based on that.

This is the solution that worked for me, and I hope this functionality can help others! You can call the variable whatever you like - mine were "UniqueClients" and "IdealProducts" and just replace those with whatever you want, and the file names with whatever files/tables you're using. 

let
    // Load the Unique Clients table based on CSV//

    UniqueClients = Table.Distinct(Table.SelectColumns(#"CLIENT CSV FILE", {"organization"})),
    
    // Load the Prodcuts CSV //

    IdealProducts = #"PRODUCTS CSV FILE",
    
    // Perform the cross join to get alist of all unique clients with a row for each ideal tool//

    CrossJoin = Table.AddColumn(UniqueClients, "Temp", each IdealProducts),
    ExpandedCrossJoin = Table.ExpandTableColumn(CrossJoin, "Temp", {"Name"}),
    #"Merged Queries" = Table.NestedJoin(ExpandedCrossJoin, {"organization", "Name"}, #"CLIENT CSV FILE", {"organization", "Name"}, "CLIENT CSV FILE", JoinKind.LeftOuter),
    #"Expanded CLIENT CSV FILE" = Table.ExpandTableColumn(#"Merged Queries", "CLIENT CSV FILE", {"Name"}, {"CLIENT CSV FILE.Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded CLIENT CSV FILE",{{"ITG Export - Applications.Name", "MatchedApplication"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Match", each if [MatchedApplication] = null then "No" else "Yes")
in
    #"Added Custom"