CODEWITHSUNDEEP
powerbidaxpowerquery
Den10102020
Den10102020

Reputation: 87

Group By and missing product value DAX Power Query

I am new to Power BI and I am trying to solve this challenge on my practice. What is the best way to solve problem like this? Dax or Power Query?

I already solved the unpivoted column to show all the products that Customer had a sales. Now, I wanted to Show all the available products that the customer never sold to.

Example Tables:

Product Table

Product 1
Product 2

Customer Table

Customer 1
Customer 2

Unpivoted Table View

Customer          Sold Products 
Customer 1        Product 1
Customer 1        Product 2
Customer 2        Product 3
Customer 3        Product 4

Desired Output

Customer               Products Possible to Sold to each Customer 
Customer 1             Product 3
Customer 1             Product 4
Customer 2             Product 1
Customer 2             Product 2

The Current Solution that I've got is Merging the Sales to the Products table and get all the items that never sold. But I wanted to see all of the available products that never sold to the Customer Level. Any help will be appreciated.

Upvotes: 0

Views: 275

Answers (1)

horseyride
horseyride

Reputation: 21393

In M/PowerQuery you can just do a merge (using left anti join) on a table of all the possible combinations against the table of ones they sold

Code for query Missing, based on Product, Customer and SoldTo queries

let Source = Customer,
#"Added Custom" = Table.AddColumn(Source, "Product", each Product),
AllPossibleCombinations = Table.ExpandTableColumn(#"Added Custom", "Product", {"Column1"}, {"Product"}),
#"Merged Queries" = Table.NestedJoin(AllPossibleCombinations, {"Column1", "Product"}, Soldto, {"Customer", "Sold Producsts"}, "Soldto", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Soldto"})
in  #"Removed Columns"

enter image description here

Upvotes: 1

Related Questions