Exel function Match in Power Query

[Arne84]

Hi, in Excel I have a function which is =IF(MATCH(V2;V:V;0)=ROW();1;0)

If it is the first entry I get a 1 otherwise a 0

How can implemente this function in Power query?

[Vijay_A_Verma]

Use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1cFNOmGQzmDShTgyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TPA_ID = _t]),
    BuffList = Source[TPA_ID],
    Result = Table.FromColumns(Table.ToColumns(Source) & {List.Generate(()=>[x=1,i=0], each [i]<List.Count(BuffList), each [i=[i]+1, x = if BuffList{i}=BuffList{i-1} then 0 else 1], each [x])},Table.ColumnNames(Source)&{"First_ID"})
    in
Result

[Arne84]

If I add a new column with your code, I got 1657904 lines. But my chart only have 90898 lines. So it duplicates th chart a few times.

[Vijay_A_Verma]

Do you have a sample file where this can be replicated?

[Anonymous]

Please try this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5dJLCoAwEAPQq0jXpSTTH+1VRHr/WygIVXDALotuZvUIJMy6GgJox2FjcaATiF+YqmRjDc1mXwg+TyR0glBjUXZRyEDK1CR1IqhUSz/JQMrMxPN6hmMXUUorZCDl9yTAN9y+LlfGc91tBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TPA_ID = _t, First_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TPA_ID", type text}, {"First_ID", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"TPA_ID"}, {{"Data", each _, type table [TPA_ID=nullable text, First_ID=nullable number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn( [Data] , "Instance" , 1 )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"TPA_ID", "First_ID", "Index", "Instance"}, {"Custom.TPA_ID", "Custom.First_ID", "Custom.Index", "Custom.Instance"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Final Column", each if [Custom.Instance] = 1 then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.First_ID", "Custom.Index", "Custom.Instance"})
in
    #"Removed Columns"

Change source step to your excel file.

Thank you.
If my answer helps you, please mark it as solution.

[Arne84]

If I add a new column with your code, I got 3419427 lines. But my chart only have 90898 lines. So it duplicates th chart a few times.

[Anonymous]

Yes, because I have grouped by in the Query. 
Can you please share an Excel file with dummy data. Will resolve your question.
Or if possible attach Power BI File in this thread.