Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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?
@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"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |