How to create a rank column using a boolean value column, by occurence of 'TRUE' in ordered table?

[saralsaral]

I have a time indexed table with a `Group`, a `Category` , and a `event_bool` . I need to plot stuff against the boolean variable, because whenever `TRUE` value occurs it is an `event` in the timeline that needs to be analyzed one against the other. After the ranking is done, I also want to fill the ranking in a way that only the `Category` variable with value `foo` gets the rank value. Thus the event time range is "expanded" out based on `Category` variable. The last two columns are expected outputs.

Edit: Ranking is to be done only when Cateogry='foo' 

Edit: sample pbix file: https://drive.google.com/file/d/1mPlsGJ1YR88YfyKkIgYBTIZuGyBDT38S/view?usp=sharing

I have previously done `Expected_ranking_output` in python, and was planning to plug in the python script with power query; however, I read in the docs that you have make the dataset public if you want to python with power query (bemused by this). Not sure if my organization will allow that. 

Group	Time_Index	Category	event_bool	Some_Plotting_Value_Col	Expected_ranking_output	Expected_Filled_Output
A	1	other	FALSE	0.929	0	0
A	2	foo	FALSE	0.929	0	1
A	3	foo	FALSE	0.209	0	1
A	4	foo	FALSE	0.191	0	1
A	5	foo	TRUE	0.071	1	1
A	6	foo	TRUE	0.507	1	1
A	7	foo	TRUE	0.101	1	1
A	8	foo	TRUE	0.661	1	1
A	9	foo	FALSE	0.936	0	1
A	10	foo	FALSE	0.611	0	1
A	11	foo	TRUE	0.397	2	2
A	12	foo	TRUE	0.677	2	2
A	13	foo	TRUE	0.407	2	2
A	14	foo	FALSE	0.473	0	2
A	15	foo	FALSE	0.411	0	2
A	16	foo	FALSE	0.012	0	2
A	17	bar	TRUE	0.080	0	0
A	18	bar	TRUE	0.149	0	0
A	19	bar	TRUE	0.748	0	0
A	20	bar	TRUE	0.812	0	0
B	1	foo	FALSE	0.734	0	1
B	2	foo	FALSE	0.917	0	1
B	3	foo	TRUE	0.734	1	1
B	4	foo	TRUE	0.755	1	1
B	5	foo	TRUE	0.854	1	1

[Jos_Woolley]

Hi,

You don't mention which values from the Category column are to be considered. It appears to be for 'foo' only, though can you please clarify? If not, and other entries in that column are also to be considered, can you explain why the four entries of 'bar' have Expected_ranking_output=0 (despite having event_bool=True)?

Regards

[saralsaral]

Yes, its for 'foo' only. The ranking is to be done only when Category is `foo`. The event_bool can be true when Category is not equal to 'foo', but I don't want to rank those values. The susbset of the data to rank is 1) The Category='foo' and event_bool=TRUE. So basically event_bool  may be ignored in interest of something like this: 

rank_able = IF(AND(Sheet1[Category]="foo", Sheet1[event_bool]=TRUE()),1,0)

[Anonymous]

I'm not bemused in the least. There are valid reasons behind this decision.

To the point... Why can't you do this - seemingly not too complex task - in Power Query WITHOUT using Python? Power Query has a powerful functional language called M where you can calculate about anything.

[saralsaral]

It might not be complex for you, it is for me. I am not comfortable with Power Query. I have extensive experience in Python, and still it took me almost half a day to do this task after fetching the data from ADX. Can you point me somewhere, is there a similar example? Are there some keywords that might help narrowing my search? 

[Anonymous]

@saralsaral 

I would have given you the M code to do it by now, had I been able to fully make sense of what you've described above. But it's not possible (at least for me). So, the upshot is this. Please try to describe your algorithm as clearly as possible, give me a good, representative set of your data, show the input and the expected output (I know you've started above but you have to be maximally clear since you know what you want to do, we don't and we don't know the ins and outs) and I'll try to write the code? How does this sound?

[saralsaral]

I'll try describing the problem more clearly. Thank you