Columns with different filters for same customer

[rit372002]

Hi,

I posted this question on another thread but didn’t get any traction as I may not have been crystal clear.

Here, I am trying again:

I am looking for customers who didn’t buy certain products. Like here: I have these customers who bought certain products (A, B, C etc…).

Here : Customer 1 bought A,B,C,D
Customer 2 bought A and C
Customer 3 bought A B and E

am looking for something like this:

Customer 1 is only prospect for Product E as they didn’t buy E.

Customer 2 is prospect for everything except A and C…so on and so forth.

I am specifically looking for this in 1 table. Is it even possible?

I have uploaded the pbix here:

https://drive.google.com/file/d/16UfeCTNx-8Ak-J2zPGeG80g3QsboBNbM/view?usp=sharing

Thanks for your help.
-RK

[MK3010]

Hi @rit372002,

With your sample pbix file I couldn’t work as it has connection to database. But I have created sample with your given example and below is the power query which does give the same output.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkMDA6VYHQjfCYiNkPjOaHwXJL4Rmn4jqHpjKN8YTd4Yaj4y3xXGjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Product = _t, CY = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Product]), "Product", "CY"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"Y",Replacer.ReplaceValue,{"A", "B", "C", "D", "E"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Customer"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Actual", each if [Value] = "Y" then [Value] else "N", type text),
    #"Replaced Value1" = Table.ReplaceValue(#"Added Custom","Y",null,Replacer.ReplaceValue,{"Value"}),
    #"Pivoted Column1" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Actual"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column1", each ([Value] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    Custom1 = Table.NestedJoin(Source, {"Customer"}, #"Removed Columns", {"Customer"}, "Sample", JoinKind.LeftOuter),
    #"Expanded Sample" = Table.ExpandTableColumn(Custom1, "Sample", {"A", "B", "C", "D", "E"}, {"Prospect for A", "Prospect for B", "Prospect for C", "Prospect for D", "Prospect for E"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Expanded Sample",null,"N",Replacer.ReplaceValue,{"Prospect for A", "Prospect for B", "Prospect for C", "Prospect for D", "Prospect for E"})
in
    #"Replaced Value2" 

Below screen shot for your reference.

Thanks
Mukesh

[rit372002]

Thanks @MK3010 but Sorry, how do I use that? Can you send me the Pbix? & even if it has database, I was under the impression that you can still create measures or columns. Sorry, I am kind of a newbie, and learn as I go through it.

Thanks!

[MK3010]

Hi @rit372002,

I am not creating measures, I am doing it by power query for which I need the actual source data.

Can you advice on which table and respective column matching to (Customer, Product and CY) you want to apply this as not clear from your post. Also to test the above code you can follow as below.

How to use it is–

1. Goto Get data
2. Click Blank Query
3. Goto Advance editor and paste the code replacing any existing code.

[Melissa]

Hi @MK3010,

Wonderful solution

@rit372002

I know your question has been answered but I wanted to explore List.Generate to achieve the same result and thought I would share that anyway for those who are interested.

Same starting position, your sample dataset

Started with a Group by “Customer” step

Then the more tricky List.Generate step, that required some actual M coding

the CheckList contains only products bought by that Customer
the ProductList contains all unique products bought by any Customer
List.Generate then ‘iterates’ over the ProductList and checks for each product if that is a product that the customer didn’t buy before and returns true/false.
Create a 2 column Table containing only Product and Prospect

Expand the generated data

Pivot the data

Performed a self Merge with the Group By step to get all the row details back

Expanded the “PivotColumns” column first

Added a prefix to the column names

And finally expanded the AllRows

Here’s your sample file: eDNA - List Generate column filters.pbix (27.4 KB)
I hope this is helpful

[MK3010]

Hi @Melissa,

Thank you for your input and really nice to see your solution. Frankly speaking I find it really difficult with List.Generate and List.Accumulate function. Can you guide me any resources you have created so I can understand the hidden gem.

Also my previous solution is not dynamic(hard coded product) so I am adding below code which is dynamic.

@rit372002 Please use the below code if you would like to.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkMDA6VYHQjfCYiNkPjOaHwXJL4Rmn4jqHpjKN8YTd4Yaj4y3xXGjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Product = _t, CY = _t]),
    Header = List.Distinct(Source[Product]),
    NewHeader = List.Distinct(Table.TransformColumns(Source,{{"Product", each Text.Insert(_, 0, "Prospect for "), type text}})[Product]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Product]), "Product", "CY"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"Y",Replacer.ReplaceValue, Header),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Customer"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Actual", each if [Value] = "Y" then [Value] else "N", type text),
    #"Replaced Value1" = Table.ReplaceValue(#"Added Custom","Y",null,Replacer.ReplaceValue,{"Value"}),
    #"Pivoted Column1" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Actual"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column1", each ([Value] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    Custom1 = Table.NestedJoin(Source, {"Customer"}, #"Removed Columns", {"Customer"}, "Sample", JoinKind.LeftOuter),
    #"Expanded Sample" = Table.ExpandTableColumn(Custom1, "Sample", Header, NewHeader),
    #"Replaced Value2" = Table.ReplaceValue(#"Expanded Sample",null,"N",Replacer.ReplaceValue,NewHeader)
in
    #"Replaced Value2"

[rit372002]

thank you @Melissa and @MK3010

I have to go through both of the solutions. How difficult is it to replicate the solution in real file scenario where I have thousands of customers and have few products? Or on the PBIX that I saved?

Thanks,
RK

[MK3010]

Hi @rit372002,

Assume that you want have this functionality on the table “CPP AND CN POS”

1. Goto advance editor
   

   

2. At the end of code before “in” add a comma (,) which was treating it as end of code. And replace the below highlighted code.

3. You need to replace the name of step matching with recent step. Hope you have all the mentioned 3 columns in this table if not then replace those with respective table.

   // Replace Product with actual column name
   Header = List.Distinct(#“Renamed Columns”[Product]),
   NewHeader = List.Distinct(Table.TransformColumns(#“Renamed Columns”,{{“Product”, each Text.Insert(_, 0, "Prospect for "), type text}})[Product]),
   #“Pivoted Column” = Table.Pivot(Source, List.Distinct(Source[Product]), “Product”, “CY”),
   #“Replaced Value” = Table.ReplaceValue(#“Pivoted Column”,null,“Y”,Replacer.ReplaceValue, Header),
   #“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Replaced Value”, {“Customer”}, “Attribute”, “Value”),
   #“Added Custom” = Table.AddColumn(#“Unpivoted Other Columns”, “Actual”, each if [Value] = “Y” then [Value] else “N”, type text),
   #“Replaced Value1” = Table.ReplaceValue(#“Added Custom”,“Y”,null,Replacer.ReplaceValue,{“Value”}),
   #“Pivoted Column1” = Table.Pivot(#“Replaced Value1”, List.Distinct(#“Replaced Value1”[Attribute]), “Attribute”, “Actual”),
   #“Filtered Rows” = Table.SelectRows(#“Pivoted Column1”, each ([Value] = null)),
   #“Removed Columns” = Table.RemoveColumns(#“Filtered Rows”,{“Value”}),
   Custom1 = Table.NestedJoin(Source, {“Customer”}, #“Removed Columns”, {“Customer”}, “Sample”, JoinKind.LeftOuter),
   #“Expanded Sample” = Table.ExpandTableColumn(Custom1, “Sample”, Header, NewHeader),
   #“Replaced Value2” = Table.ReplaceValue(#“Expanded Sample”,null,“N”,Replacer.ReplaceValue,NewHeader)
   in
   #“Replaced Value2”

[Melissa]

Hi @MK3010,

Only used List.Generate here and in Challenge #5
You can review that here and I will do my best to work on more content

Power BI Builds 5 - Optical Data

. Sounds like a nice Power Query challenge In my submission I explored using a recursive function, to return a future date - you can find that here. . The M function below generates a list of dates, so you can extract whatever you find interesting ( myDate as date, dateDOB as date ) as list => let BufferedInterval = Table.Buffer(Interval), curDate = Date.AddMonths( myDate, Table.SelectRows( BufferedInterval, (BT) => BT[Age] = Number.RoundDown( Duration.TotalDays…

[rit372002]

@MK3010

I am getting this literal error and I followed your step. I put this in a file that I sent earlier.

Thanks,
Ritesh

[rit372002]

@MK3010 @Melissa

That makes me wonder, is there a solution for it without M code?

Thanks a lot for you guys help so far!

Ritesh

[MK3010]

Hi @rit372002,

You are getting error because you just copy pasted the code and when you do so the double quote will be different than the actual one. You need to replace all the instance of that. Best is copy paste in notepad and replace the opening double quote.

Select the opening double quote and press Cntl+H and enter the double quote.

Select the closing double quote and press Cntl+H and enter the double quote.

Or alternatively you can use the pbix file shared by Melisa.

Sorry for not uploading the pbix file as I am using my office system.

Thanks
Mukesh

[rit372002]

@MK3010 Thanks for sincere responses.

I was able to overcome this by some dax formula.

Fungicide Prospect = VAR _CROPYEAR =
CALCULATE ( MAX ( 'CPP AND CN POS'[Crop Year] ), ALL ( 'CPP AND CN POS' ) )VAR bought_fungicide =
CALCULATE (
    SUM ( 'CPP AND CN POS'[Sales Amount] ),
    FILTER (
        SUMMARIZE ( 'CPP AND CN POS', 'PRODUCT'[PRODUCT],'CPP AND CN POS'[Crop Year],GROWER[Parent Grower Master Key] ),
        AND (
            AND (
                'PRODUCT'[PRODUCT] = "Fungicide",
                max('CPP AND CN POS'[Crop Year]) = _CROPYEAR
            ),
            
               and( [PRODUCT SALES CY for CROP YEAR] > 0,
               NOT ( ISBLANK ( [PRODUCT SALES CY for CROP YEAR] ) )))))
            
RETURN
bought_fungicide

And then with this formula:

F Prospects = if((ISBLANK(('CPP AND CN POS'[Fungicide Prospect]))),"Y", "N") 

And same way I created 2 more formulaes for 2 other products. And was able to use those as columns.

Thanks again,
Ritesh

[MK3010]

Hi @rit372002

Great, so seems you need to create measure for each product. As long as the number of product is constant it is good but if number of product increased then you need to do it again?

I would suggest you create something which should be future proof. But it’s upto individual choice.

[EnterpriseDNA]

Hi @rit372002, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!