Looping in PQE

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

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