Forum Discussion

slinfoo4's avatar
slinfoo4
Frequent Visitor
2 years ago
Solved

Rationalise Rows

Hi  I'm trying to work out how to achieve the following in DAX (or Power Query) that I currently do using VBA in excel. I have a table with data similar to the below: Distance From Distance To...
  • lbendlin's avatar
    lbendlin
    2 years ago

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIE4qDUFKVYnWgw2wiJD2KbALF7UWpqHlgExDM0QBECcw2NUcVAXEMTZKNBHGNjJBEwxwSk2SmnNBViOohnYopqI4hrYoYqBuKaGsAMiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Distance From" = _t, #"Distance To" = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Distance From", Int64.Type}, {"Distance To", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"Distance From", each List.Min([Distance From]), type nullable number}, {"Distance To", each List.Max([Distance To]), type nullable number}},GroupKind.Local)
in
    #"Grouped Rows"

    obligatory credit: ImkeF 

lbendlin's avatar
lbendlin
Super User
2 years ago

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIE4qDUFKVYnWgw2wiJD2KbALF7UWpqHlgExDM0QBECcw2NUcVAXEMTZKNBHGNjJBEwxwSk2SmnNBViOohnYopqI4hrYoYqBuKaGsAMiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Distance From" = _t, #"Distance To" = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Distance From", Int64.Type}, {"Distance To", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"Distance From", each List.Min([Distance From]), type nullable number}, {"Distance To", each List.Max([Distance To]), type nullable number}},GroupKind.Local)
in
    #"Grouped Rows"

obligatory credit: ImkeF 

  • slinfoo4's avatar
    slinfoo4
    Frequent Visitor
    2 years ago

    I always discounted using group. I guess I missed reading up on the GroupKind parameter.

    Thanks that worked a treat.