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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors