cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alfranco17
Frequent Visitor

Find position in a list in Power Query.

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.

1 REPLY 1
Ashish_Mathur
Super User III
Super User III

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.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors