cancel
Showing results for
Did you mean:

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

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
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:

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!

Super User

Hi Scott,

This part of the code is the source:

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:

Pete

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

Proud to be a Datanaut!

4 REPLIES 4
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

Super User

Hi Scott,

This part of the code is the source:

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:

Pete

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

Proud to be a Datanaut!

Community Support

Hi @PaisleyPrince ,

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

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:

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!

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors