cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmclej
Helper I
Helper I

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

Hello,

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

IdRefUserCreationdateIsuserpartofteama
1titijohn2022-05-23 11:05:40.359 +0200TRUE
1titisam2022-05-20 17:09:45.832 +0200FALSE
1titipat2022-05-20 11:59:44.275 +0200FALSE
2tutusam2022-06-20 16:33:14.683 +0200FALSE
2tutupat2022-06-17 15:48:29.721 +0200FALSE

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.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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.

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) ?

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.

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors