Forum Discussion
Flag based on all data in the table
Hello
I have the following dataset
| CustomerID | ProductID |
| 000AA | 1 |
| 000CC | 3 |
| 000CC | 1 |
| 000FF | 3 |
| 000BB | 1 |
| 000DD | 1 |
| 000FF | 2 |
What I'm trying to do is to add a column that will flag for the customer whether they have other purchases in the table, so I want my final dataset to look like this
| CustomerID | ProductID | Product1Flag | Product2Flag | Product3Flag |
| 000AA | 1 | Y | N | N |
| 000CC | 3 | Y | N | Y |
| 000CC | 1 | Y | N | Y |
| 000FF | 3 | N | Y | Y |
| 000BB | 1 | Y | N | N |
| 000DD | 1 | Y | N | N |
| 000FF | 2 | N | Y | Y |
Notice that CustomerID 000CC has a flag in column Product1Flag and Product3Flag even when that record appears before their purchase of Product 1.
In the end that table should give me all the products purchased by that customer in a single row.
Im very very new to PowerBI and DAX functions - i recognize this is probably very easy so thank you for your patience here
2 Replies
- amitchandak
Super User
Anonymous , Please try this power query code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwcHRU0lEyVIrVAfOcnYE8YxQeXM7NDVnOyQlZzsUFQ6WRUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, ProductID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", type text}, {"ProductID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Cnt", each 1),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"ProductID", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"ProductID", type text}}, "en-US")[ProductID]), "ProductID", "Cnt", List.Max),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"1", type text}, {"3", type text}, {"2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","1","Y",Replacer.ReplaceText,{"1", "3", "2"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"N",Replacer.ReplaceValue,{"1", "3", "2"})
in
#"Replaced Value1" Hello amitchandak
The issue is me here. Very new to this. Where exactly do I include this large code set and which fields do i modify to adjust to my actual query?
