Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.