Looping in PQE

[PaisleyPrince]

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

[BA_Pete · Accepted solution]

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

[PaisleyPrince]

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

[BA_Pete · Accepted solution]

Hi Scott,

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

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

[v-cgao-msft]

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 · Accepted solution]

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