how to loop through a table and work only on a subset of it "little by little" till the end of table

[jmclej]

Hello,

I have a query which leads to a table like this :

Id	Ref	User	Creationdate	Isuserpartofteama
1	titi	john	2022-05-23 11:05:40.359 +0200	TRUE
1	titi	sam	2022-05-20 17:09:45.832 +0200	FALSE
1	titi	pat	2022-05-20 11:59:44.275 +0200	FALSE
2	tutu	sam	2022-06-20 16:33:14.683 +0200	FALSE
2	tutu	pat	2022-06-17 15:48:29.721 +0200	FALSE

I want to add a colum where, per Id, I would write the time elapsed between Creationdate of the line where Isuserpartofteama is true and the previous line in time (It is already sorted on Creationdate desc), 0 if no Isuserpartofteama is true. 

What I don't know is how to pass a subset of my table that would contain all the lines with same Id, work ma date calculation on that subset, and go on like this for all the distinct Ids of my original table.

Is there maybe a way to call a function with Id as a parameter and loop in some way ? Would that be easier/more readable or preferable to do all within a new step ?

Thank you for your suggestions.

[lbendlin · Accepted solution]

You get the loop for free in Power Query.  Use Table.AddColumn with a custom column generator.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc+5DsIwEATQX7HcElt7+Ii3o4CKiqOKUqQjSBwSzv9jKFAwiGqK0dNouk6jbnQe81jidD1eShAQGfCGWCEKeHFg2Se1AAIo/X57WOm++aD34TyXoDAKJHHetkxvuV5udl/0NuSKovhCnaXof1B60ilP1Wp40SDMgs6Glv/T+WowGBWWn61QspGwpv0D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Ref = _t, User = _t, Creationdate = _t, Isuserpartofteama = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Ref", type text}, {"User", type text}, {"Creationdate", type datetimezone}, {"Isuserpartofteama", type logical}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> Table.SelectRows(#"Changed Type",each [Id]=k[Id]))
in
    #"Added Custom"

From there you can then apply your desired logic and return the elapsed time or 0.

[jmclej]

Thank you, it helped me, even if I don't understand what this bit of code does :

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Ref = _t, User = _t, Creationdate = _t, Isuserpartofteama = _t]

?

Anyway, I've continued my logic but got weirdly stuck with another issue that I posted here : https://community.powerbi.com/t5/Desktop/Table-FirstN-function-doesn-t-seem-to-work-properly-for-my-... Would you mind taking a look please (I have added more data using your compression system) ?

[lbendlin]

the code you reference is boilerplate Power Query code for table column type declarations - you can ignore that.  Will have a look at your other request.