Forum Discussion
Solved
List.Max DirectQuery alternative to Group By
I am attempting to list the max number in field [seg_id] based on the content of field [Identifier], as below. You can see where [Identifier] "FG99395" has two different values in [seg_id]. I ...
Hi PMyers, I'm not sure if this will work with DirectQuery mode (I've never used it) but try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnM3VNJRMjRQitWBcYxgHCMgxxLGMQZyTAyQeRYWFkg8U5BcLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Identifier = _t, seg_id = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"seg_id", Int64.Type}}), Ad_MaxSegId = Table.AddColumn(ChangedType, "max seg_id", each List.Max(Table.SelectRows(ChangedType, (x)=> x[Identifier] = [Identifier])[seg_id]), type number) in Ad_MaxSegId
Hi PMyers, I'm not sure if this will work with DirectQuery mode (I've never used it) but try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnM3VNJRMjRQitWBcYxgHCMgxxLGMQZyTAyQeRYWFkg8U5BcLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Identifier = _t, seg_id = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"seg_id", Int64.Type}}),
Ad_MaxSegId = Table.AddColumn(ChangedType, "max seg_id", each List.Max(Table.SelectRows(ChangedType, (x)=> x[Identifier] = [Identifier])[seg_id]), type number)
in
Ad_MaxSegIdThis code works almost perfectly. I say almost, as sadly it doesn't work in DirectQuery. It appears I will have to find an alternative way of doing what I hoped to do. My thanks to you for providing the backend code. I will mark this as a solution, as the code is fine, even though nothing seems to do what I am after in DirectQuery mode.
Thanks again for your help.
Pete
You're welcome.
