Find position in a list in Power Query.

[alfranco17]

Hi.

I have a list like the first two columns and need to get the fourth one:

File	Employee	Index	Number
First	Tom	1	1
Last one	Tom	2	2
Week 1	Clark	3	1
March	Clark	4	2
April	Clark	5	3

I found Table.SelectRows and Table.RowCount. So I thought I could use something like:

= Table.RowCount(Table.SelectRows(#"My table", each ([Employee] = "Clark" and [Index] < 4)))

I don't know how I could substitute "Clark" with "the value for [Employee] in this record" and 4 for "the value of [Index] in the current row".

Is there a way to tell Power Query to look for the value of the field in the current record?

Is there an easier way to do it? Maybe there is a function that does exactly this and I don't know about it.

Thanks!

Armando.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File", type text}, {"Employee", type text}}),
    Partition = Table.Group(#"Changed Type", {"Employee"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"File", "Index"}, {"File", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Partition",{"File", "Employee", "Index"})
in
    #"Reordered Columns"

Hope this helps.