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

[tbdbitl08]

Hello - I am looking to create a comparative analysis for a sales team. They have provided two data sets in CSV format - the first dataset is a list of all clients where each row contains the client name (org), a product sold (product), and the product category (category). The second sata set is their "ideal state" of products they think all clients should have. The goal is to create a new table that shows what products are missing, by each client, from the ideal state so they know what to try to sell to them. I have included some sample datasets below and what I am trying to show in the expected output. I am unsure if there is a way to do this, or visualize it etc. At the end of the day, we want to be able to show what a customer is missing, easily, so the team can know what the client needs, like in the Example Output, without having to manually create a new table. 

[v-menakakota · Accepted solution]

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.

[grazitti_sapna]

Hi @tbdbitl08 ,
Please try using:-

MissingProducts = 
VAR AllClients = DISTINCT('Example Client Table'[Org])
VAR AllProducts = 'Example Ideal State Table'
RETURN
ADDCOLUMNS(
FILTER(
CROSSJOIN(AllClients, AllProducts), 
NOT (
'Example Client Table'[Org] = [Org] &&
'Example Client Table'[Product] = [Product]
)
),
"Org", [Org],
"Product", [Product],
"Category", [Category]
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!

[tbdbitl08]

Just giving this a little judge to see if yourself, or anyone else, has any other suggestions. Thank you!

[v-menakakota]

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

We really apologies for the inconvenience, after reviewing the issue by using the concept of merging followed the below steps  and got the result like below which i have shared the pbix file,please go through the file:

• Grouped the data by Client Name to isolate available products for each client.

• Separated the grouped data into individual tables for comparison against an ideal product list.

• Performed a Right Anti Join between each client-specific table and the ideal product table to identify missing products. (The Ideal table was used as the right table in the join.)

• Repeated the process for each client to ensure full coverage.

• Appended the results from all clients into a consolidated table and cleaned up by removing any unnecessary columns.

  

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[tbdbitl08]

Hello and thank you for the help! Does there happen to be a way to do this without creating individual client tables? The file you gave does eventually come up with a correct way to manage the visual, however the actual dataset I am working with has over 100 clients and 500 products, and I am unsure that it would be viable to create separate tables for each client. Those client list and product lists is updated weekly and exported into a CSV, while the ideal state table is updated quarterly and exported to a CSV. 

[v-menakakota · Accepted solution]

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.

[tbdbitl08 · Accepted solution]

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"

[v-menakakota]

Hi @tbdbitl08 ,

We really appreciate your efforts and for letting us know the update on the issue.

Please continue using fabric community forum for your further assistance.

 If this is the solution that has worked for you please accept your reply as solution so as to help other community members who may face similar issue in the future

Thank you and Regards,
Menaka Kota.

[v-menakakota]

Hi @tbdbitl08 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @tbdbitl08 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi  @tbdbitl08 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[tbdbitl08]

Hi there! When I go to create a new table and add this in, and change the appropriate file/table names I get the equivalent of an error "The ambiguous column reference [organization] can't be resolved." and am a bit unsure as to my next steps. Is there a need to create any relationships, or is there something else I may be missing? Thank you!