Flag first occurrence item in Power Query

[WencyREN]

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 environment? The [flag first occurrence of the product type] column below is the expected outcome.

Thank you in advance!

Product	XXX	XXX	XXX	[flag first occurrence of the product type]
A	XXX	XXX	XXX	1
A	XXX	XXX	XXX	0
A	XXX	XXX	XXX	0
B	XXX	XXX	XXX	1
C	XXX	XXX	XXX	1
C	XXX	XXX	XXX	0
D	XXX	XXX	XXX	1
D	XXX	XXX	XXX	0
B	XXX	XXX	XXX	0
C	XXX	XXX	XXX	0
D	XXX	XXX	XXX	0

[mikekim123]

is it possible to get the last occurence flag using any similar method

[arnabmit]

Hi @Anonymous @Jakinta 

I have a similar query; I am trying to flag the first occurrence of a combination of values from two different columns.

Thanks for any guidance!

Product	Month	First Occurrence
A	1	1
A	1	0
A	2	1
B	1	1
C	1	1
C	2	1
D	1	1
D	1	0
B	1	0
C	2	0
D	2	1

[Jakinta]

Try the following code with replacing the Source step with your query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToText = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Month", type text}}),
    List = List.Transform( List.Zip ( {ToText[Product], ToText[Month]} ),  Text.Combine),
    #"Added Custom" = Table.AddColumn(ToText, "Custom", each List.PositionOf( List, [Product]&[Month])),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "First Occurrence", each Number.From( [Custom]=[Index] )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index"})
in
    #"Removed Columns"

Result

[Anonymous]

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.

[Jakinta · Accepted solution]

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"