Rationalise Rows

Question

Hi&nbsp;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 FromDistance ToType01Red12Red24Green410Green1013Green1314Red1433Red3340Blue4045Green4546Green4650RedAnd need to rationalise it to the below.&nbsp;Distance FromDistance ToType02Red213Green1333Red3340Blue4046Green4650Red&nbsp;So currently I do this in VBA by looping through the rows and keeping track of the Min [Distance From] from and Max [Distance To] until the [Type] changes. The data is alway sorted on [Distance From] ascending and the [Distance To] always matches the next rows [Distance From]

lbendlin · Accepted Answer

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:&nbsp;ImkeF&nbsp;

Forum Discussion

Rationalise Rows

2 Replies

Related content

remove duplicate value within a row

Matrix Row Conditional Formatting

Where is the Multi-row card

Issue with Formulas - When Multiple Rows Similar Rows Exist

Filter rows

Distance From	Distance To	Type
0	1	Red
1	2	Red
2	4	Green
4	10	Green
10	13	Green
13	14	Red
14	33	Red
33	40	Blue
40	45	Green
45	46	Green
46	50	Red