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

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

@ImkeF

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

Super User
Super User

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

Please check the name of the previous step or paste your full M-query-code 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

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

Ajay123 Regular Visitor
Regular Visitor

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

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.

Super User
Super User

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

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

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




aviral Regular Visitor
Regular Visitor

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

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.
Super User
Super User

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

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

 

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




aviral Regular Visitor
Regular Visitor

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

Thanks a lot @ImkeF

 

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

 

Can't thank you enough for this help. 

Highlighted
Super User
Super User

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

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.

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