Forum Discussion

WencyREN's avatar
WencyREN
Frequent Visitor
4 years ago
Solved

Flag first occurrence item in Power Query

Dear, I have a sample dataset as shown below, could you please instruct me on how to add a custom column to flag the first occurrence of the product type (as 1, else as 0) in the Power Query environm...
Need Help
  • Jakinta's avatar
    Jakinta
    4 years ago

    This can help.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYqIiEAhY3VIF3fCIe5MorgLieKk2ovVnFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, XXX = _t, XXX.1 = _t, XXX.2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.PositionOf( Source[Product], [Product]) ),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Flag", each Number.From([Custom]=[Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index"})
in
    #"Removed Columns"

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi WencyREN ,

 

Please firstly try Jakinta 's method.

 

My workaround is adding Index column and using "Group by":

You could create a blank query and paste the following M syntax to Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYqIiEAhY3VIF3fCIe5MorgLieKk2ovVnFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"A", type text}, {"B", type text}, {"C", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Product"}, {{"Count", each _, type table [Product=nullable text, A=nullable text, B=nullable text, C=nullable text, Index=number]}, {"Min", each List.Min([Index]), type number}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"A", "B", "C", "Index"}, {"A", "B", "C", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Index]=[Min] then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Min"})
in
    #"Removed Columns"

Output:

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.