Forum Discussion
Create Gap Analysis Table From Two Tables - Raw Data and Ideal State
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.
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"
11 Replies
- grazitti_sapna
Super User
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!
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!
Just giving this a little judge to see if yourself, or anyone else, has any other suggestions. Thank you!
v-menakakotaCommunity Support
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.
-
