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
PaisleyPrince
Helper I
Helper I

Looping in PQE

Hi, I have a problem that i'm trying to solve using PQE but it may be equally best solved in DAX. I'm trying to loop over a series of rows and get a cumulative sum for a pair of values. Sample input is as follows

Dayvalue item 1 item 2

1764         2         4

1765         1         2

1764         1         3

1766         2         3

1767         1         2

 

What i'd like is a summary table showing the dayvalue and the amount of times that each pair of item 1 and item 2 has occurred in the last 8 dayvalues, e.g.,

Dayvalue item 1 item 2 Count

1767         1          2            2

1767         2          4            1

Any help would be much appreciated.

thanks

Scott

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi Scott,

 

Try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3M1HSUTICYhOlWB2wgCmQYwgWhAqYQAWMYQJmUC1wAXOEllgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dayvalue = _t, #"item 1" = _t, #"item 2" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Dayvalue", Int64.Type}, {"item 1", Int64.Type}, {"item 2", Int64.Type}}),

    sortDayValue = Table.Sort(chgTypes,{{"Dayvalue", Order.Ascending}}),
    addLast3 = Table.AddColumn(sortDayValue, "last3", each if List.Contains(List.LastN(sortDayValue[Dayvalue], 3), [Dayvalue]) then "last3" else null),
    filterNullLast3 = Table.SelectRows(addLast3, each [last3] <> null),
    groupRows = Table.Group(filterNullLast3, {"item 1", "item 2", "last3"}, {{"Dayvalue", each List.Max(filterNullLast3[Dayvalue]), type nullable number}, {"Count", each Table.RowCount(_), Int64.Type}}),

    remOthCols = Table.SelectColumns(groupRows,{"Dayvalue", "item 1", "item 2", "Count"})
in
    remOthCols

 

...to get this output:

BA_Pete_0-1683646032914.png

 

I've done it based on the last 3 Dayvalues, as there weren't more than 8 in your example data.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

Hi Scott,

 

Sorry for late reply, I somehow missed your post in my notifications.

This part of the code is the source:

BA_Pete_0-1684302519969.png

 

The easiest thing to do would be to connect to your actual source in another query, then copy all the steps from that query after the 'let' statement and before the 'in' statement and paste it over the highlighted bit above.

This is assuming that your actual query has the same structure and column names as I've used in my example. If not, then you'll either need to change your column names before my code, or go through my code amending the column name references to your actual column names, here:

BA_Pete_1-1684302807635.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
PaisleyPrince
Helper I
Helper I

Hi Pete,

Thanks for your answer. I've had trouble implementing it on my data and its not giving me expected results. How should the source statement be amended to use another query / table ?

thanks

Scott

Hi Scott,

 

Sorry for late reply, I somehow missed your post in my notifications.

This part of the code is the source:

BA_Pete_0-1684302519969.png

 

The easiest thing to do would be to connect to your actual source in another query, then copy all the steps from that query after the 'let' statement and before the 'in' statement and paste it over the highlighted bit above.

This is assuming that your actual query has the same structure and column names as I've used in my example. If not, then you'll either need to change your column names before my code, or go through my code amending the column name references to your actual column names, here:

BA_Pete_1-1684302807635.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi @PaisleyPrince ,

About how to integrate M-code into your existing solution:

Power BI Forum Help: How to integrate M-code into ... - Microsoft Power BI Community

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

BA_Pete
Super User
Super User

Hi Scott,

 

Try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3M1HSUTICYhOlWB2wgCmQYwgWhAqYQAWMYQJmUC1wAXOEllgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dayvalue = _t, #"item 1" = _t, #"item 2" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Dayvalue", Int64.Type}, {"item 1", Int64.Type}, {"item 2", Int64.Type}}),

    sortDayValue = Table.Sort(chgTypes,{{"Dayvalue", Order.Ascending}}),
    addLast3 = Table.AddColumn(sortDayValue, "last3", each if List.Contains(List.LastN(sortDayValue[Dayvalue], 3), [Dayvalue]) then "last3" else null),
    filterNullLast3 = Table.SelectRows(addLast3, each [last3] <> null),
    groupRows = Table.Group(filterNullLast3, {"item 1", "item 2", "last3"}, {{"Dayvalue", each List.Max(filterNullLast3[Dayvalue]), type nullable number}, {"Count", each Table.RowCount(_), Int64.Type}}),

    remOthCols = Table.SelectColumns(groupRows,{"Dayvalue", "item 1", "item 2", "Count"})
in
    remOthCols

 

...to get this output:

BA_Pete_0-1683646032914.png

 

I've done it based on the last 3 Dayvalues, as there weren't more than 8 in your example data.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors