cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gvg Member
Member

How to get an index of a value in list

Hi,

 

I am wondering how do I get an index of a value in a list? Say, I have a separate list called myList with the following values:

 

A

B

C

 

How do I get position of C (which is 3) in this list ("M" or DAX)?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: How to get an index of a value in list

Say you have Column1 and Column2 in your table already, then you add Column3 with an expression like this:

List.PositionOf(Table1, [Column2])+1

 

Or the full formula:

Table.AddColumn(PreviousStep, "Column3", each List.PositionOf(Table1, [Column2])+1)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




18 REPLIES 18
Super User
Super User

Re: How to get an index of a value in list

Hi @gvg

 

You could add an index colum from the Query Editor's "AddColumn" Tab>>Index Column>>from 1

 

Do you need something else?

gvg Member
Member

Re: How to get an index of a value in list

@Zubair_Muhammad

I have the list as a separate list. I am looking how to extract that index from within another query.

Super User
Super User

Re: How to get an index of a value in list

@gvg

 

I am not sure..but try this DAX Measure

 

Position Measure =
CALCULATE (
    RANKX ( ALL ( List ), CALCULATE ( VALUES ( List[Column] ) ),, ASC, DENSE ),
    List[Column] = "C"
)
gvg Member
Member

Re: How to get an index of a value in list

@Zubair_Muhammad

 

Was thinking of using somehow List.PositionOf in "M", but I do not know how to invoke external list in this function.

Super User
Super User

Re: How to get an index of a value in list

Hi @gvg

 

Tagging the gurus of PQuery for you

 

@ImkeF@MarcelBeug

gvg Member
Member

Re: How to get an index of a value in list

@Zubair_Muhammad

Thanks! Hope she will respond. I know how to extract value if you know index, but not vice versa.

Super User
Super User

Re: How to get an index of a value in list

List.PositionOf is your friend here:

 

List.PositionOf({1,2,3,3}, 3) will return 2 (M uses zero-based indexing)

List.PositionOf({1,2,3,3}, 3, 2) will return {2,3}, so a list of all positions where the value is found

 

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




gvg Member
Member

Re: How to get an index of a value in list

@ImkeF

That's ok. But how do I reference a list that is outside of my query? Say, I have a list in Table1, and want to build Table2 based on list in Table1. I need to put in Table2 positions of A,B and C in the list (like in Column3):

 

Pic1.jpg                      Pic2.jpg

Highlighted
Super User
Super User

Re: How to get an index of a value in list

Say you have Column1 and Column2 in your table already, then you add Column3 with an expression like this:

List.PositionOf(Table1, [Column2])+1

 

Or the full formula:

Table.AddColumn(PreviousStep, "Column3", each List.PositionOf(Table1, [Column2])+1)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries