Forum Discussion
Post Prodigy[Power Query] How to get the first non-zero value in a table?
Using the sample table below - is it possible to get the first non-zero value using Power Query?
I understand I can possibly do this via DAX but there are over 100k rows and it seems wasteful to load that much data into the report so I am taking the PQ route. I also understand, I can possibly use Pivot in this case however, the RN (Row Number) goes as far as RN=39. I'm thinking maybe convert the VAL column to a text column and then merge them into one but how do I make sure I am getting all pivoted VAL columns upon refresh?
| ID | VAL | RN | ||
| 1001404038 | 2393.64 | 1 | ||
| 1001459253 | 562.25 | 1 | ||
| 1001463837 | 0 | 1 | ||
| 1001463837 | 3252.24 | 2 | ||
| 1001478049 | 4606.74 | 1 | ||
| 1001478049 | 4601.74 | 2 | ||
| 1001478049 | 4601.74 | 3 | ||
| 1001478049 | 4601.74 | 4 |
Output should be like this:
| 1001404038 | 2393.64 | 1 | ||
| 1001459253 | 562.25 | 1 | ||
| 1001463837 | 3252.24 | 2 | ||
| 1001478049 | 4606.74 | 1 |
and you can useed this code
Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.FirstN( Table.SelectRows(_, each _[VAL] > 0),1) }})
4 Replies
- Ahmedx
Super User
it's not very clear what you want
explain again and provide an example of the result you want to get
olimiloTHanks, I've edited the post to show the requested output.
- Ahmedx
pls see my video
https://1drv.ms/v/s!AiUZ0Ws7G26Ringrxdxj3drlNpFE?e=8vMDu4
=Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.SelectRows( Table.AddIndexColumn( Table.SelectRows(_, each _[VAL] > 0),"Indx",1,1), each _[Indx]=1) }}) - Ahmedx
and you can useed this code
Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.FirstN( Table.SelectRows(_, each _[VAL] > 0),1) }})