Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
Solved! Go to 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
Hi @gvg
You could add an index colum from the Query Editor's "AddColumn" Tab>>Index Column>>from 1
Do you need something else?
I have the list as a separate list. I am looking how to extract that index from within another query.
I am not sure..but try this DAX Measure
Position Measure = CALCULATE ( RANKX ( ALL ( List ), CALCULATE ( VALUES ( List[Column] ) ),, ASC, DENSE ), List[Column] = "C" )
Was thinking of using somehow List.PositionOf in "M", but I do not know how to invoke external list in this function.
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
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):
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
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!
Thanks! Hope she will respond. I know how to extract value if you know index, but not vice versa.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |