Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gvg
Post Prodigy
Post Prodigy

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

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)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

18 REPLIES 18
Zubair_Muhammad
Community Champion
Community Champion

Hi @gvg

 

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

 

Do you need something else?


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

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

@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"
)

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

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

Hi @gvg

 

Tagging the gurus of PQuery for you

 

@ImkeF@MarcelBeug


Regards
Zubair

Please try my custom visuals

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@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

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)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF

Tried that. Get Expression.Error: A cyclic reference was encountered during evaluation.

Please check the name of the previous step or paste your full M-query-code here.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I think I figured out my problem. I have built my list from the table in which I reference that list.
Thanks!

@Zubair_Muhammad

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

Anonymous
Not applicable

Hi,

 

This must be pretty simple though.

 

Could you pls help me on how to fetch a particular value from a List object by passing a Index value.

 

For ex, If I have a list Test{"A""B""C""D""E"}, how do I fetch the third value("C") inposition "3" by sending a command like Test(3) or Test{3}.

 

Thanks in advance.

Test{2} will give you the 3rd position, because M starts to count at zero.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I have a column which has null/blank values which have to be replaced with the average of last 2 quarters.
myList={1, BLANK, 3, 4, BLANK, 6, 7, BLANK, BLANK, BLANK, 10}
The result that I am looking for is
result= {1, 1, 3, 4, 3.5, 6, 7, 6.5, 6.75, 6.625, 10}
 
Could you please help me achive this?
 
I am trying to use List.Transform but unless I get the position of the current item, I would not be able to figure out the numbers that I should average. List.PositionOf for "BLANK" returns the position of first "BLANK" in the list which is not helping.
 
List.Positions says (https://docs.microsoft.com/en-us/powerquery-m/list-positions) but unable to find an example of usage.
Remarks
When using List.Transform to modify a list, the list of positions can be used to give the transform access to the positions.

Yes, you need some sort of recursive action here (either real recursion, using @, List.Generate or List.Accumulate).

 

I'm using List.Generate here:

 

let
    Source = {1, null, 3, 4, null, 6, 7, null, null, null, 10},
    List0 = List.Generate(
                ()=> [Result = Source{0}, Previous = Source{0}, Counter = 0],
                each [Counter] <= List.Count(Source),
                each [
                    Result = if Source{[Counter]} = null then ([Result] + [Previous]) / 2 else Source{[Counter]},
                    Previous = [Result],
                    Counter = [Counter] + 1    
                ],
                each [Result]
    ),
    MyList = List.Skip(List0, 1)
in
    MyList

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks a lot @ImkeF

 

You have set me on a completely different level with the @ hint. 

 

Can't thank you enough for this help. 

You're welcome @aviral,

but real recursion is not recommended in Power Query, as it is slow.

List.Generate or List.Accumulate are faster performing alterntives.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.