Forum Discussion
EARLIER function in M/Query Editor
Hi
I have data that looks like this:
| ID | Name |
| 1 | Apple |
| 1 | Banana |
| 2 | Orange |
| 3 | Pear |
| 4 | Pineapple |
| 4 | Grape |
| 4 | Mango |
| 5 | Peach |
I want it to look like this:
| ID | Name1 | Name2 | Name3 |
| 1 | Apple | Banana | |
| 2 | Orange | ||
| 3 | Pear | ||
| 4 | Pineapple | Grape | Mango |
| 5 | Peach |
I plan to do this by ranking the names per ID, e.g.
| ID | Name | Rank |
| 1 | Apple | 1 |
| 1 | Banana | 2 |
| 2 | Orange | 1 |
| 3 | Pear | 1 |
| 4 | Pineapple | 1 |
| 4 | Grape | 2 |
| 4 | Mango | 3 |
| 5 | Peach | 1 |
...then pivoting the table using the rank field. I don't know if this will work, but to do it I need to use something like the EARLIER function in DAX. Anyone know an alternatinve in M? (Or a better way to achieve my end goal).
Thanks!
HI bullius,
Yes it is possible to do that in M.
To sum up group by your id and add an index.
You can add this code into your steps:
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1), type table}})Hope it helps...
Ninter
9 Replies
Thanks prateekraina. My actual dataset is much larger than the example and does not include a Rank field. My question is how do I create one? I would use the EARLIER function in DAX to make sure the ranking restarts with each new ID, but can't find an alternative in M.
HI bullius,
Yes it is possible to do that in M.
To sum up group by your id and add an index.
You can add this code into your steps:
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1), type table}})Hope it helps...
Ninter
Hi Patreek.
I have the same question. If you have it at hand, would you please repost your pbix?
Thank you.
Armando.
Hi,
Share some data and show the expected result.
