Merge range dates in Power Query.

[J4neX]

Hello, 

I have a problem with my reports. For example i have a table:

SA_ID                  START_DT          END_DT

8702145434	2023-05-05	2023-05-08
8702145434	2023-05-08	2023-05-09
8702145434	2023-05-09	2023-05-11
8702145434	2023-05-12	2023-05-15
8702145434	2023-05-19	2023-05-22
8702145434	2023-05-23	2023-05-23
8702145434	2023-05-26	2023-05-29
1132145497	2023-05-05	2023-05-08
1132145497	2023-05-12	2023-05-15
1132145497	2023-05-16	2023-05-22
1132145497	2023-05-26	2023-05-29

I would like connect dates where END_DT one row is the same or one day earlier than START_DT next row grouping by SA_ID.

Below is the effect I would like to achieve.

SA_ID                  START_DT         END_DT

8702145434	2023-05-05	2023-05-15
8702145434	2023-05-19	2023-05-23
8702145434	2023-05-26	2023-05-29
1132145497	2023-05-05	2023-05-08
1132145497	2023-05-12	2023-05-22
1132145497	2023-05-26	2023-05-29

I've tried everything, searched google and used gpt chat, however I still don't know how to do it. Please help !!!!

[slorin · Accepted solution]

Hi

let
Source = YourSource,
#"End+1" = Table.AddColumn(Source, "END+1", each Date.AddDays([END_DT],1)),
Join0 = Table.NestedJoin(#"End+1", {"SA_ID", "START_DT "}, #"End+1", {"SA_ID", "END_DT"}, "Join0", JoinKind.LeftOuter),
Join1 = Table.NestedJoin(Join0, {"SA_ID", "START_DT "}, Join0, {"SA_ID", "END+1"}, "Join1", JoinKind.LeftOuter),
Test = Table.TransformColumns(Join1,{{"Join0", each Table.IsEmpty(_), type logical},{"Join1", each Table.IsEmpty(_), type logical}}),
LocalGroup = Table.Group(Test,
   {"SA_ID", "Join0", "Join1"}, 
   {{"START_DT", each List.Min([#"START_DT "]), type nullable date},
    {"END_DT", each List.Max([END_DT]), type nullable date}},
   GroupKind.Local,
   (x,y) => if y[Join0] and y[Join1] then 1 else 0)
in
LocalGroup

Stéphane 

[J4neX]

Hello, 

I don't understand one line, Could you explain plesase? Why have we (x,y) but use only y[join0] and y[join1]?

(x,y) => if y[Join0] and y[Join1] then 1 else 0)

[slorin]

x = first row of group

y = actual row

if y[join0] and y[join1] are true then create new group else group row y with x

Stéphane

[Papermain]

Hello,

Could you kindly explain what you do in the LocalGroup step of you query? I am trying to understand how the logic works, but having some trouble. Thanks!

[slorin]

Look here

https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

sort by ID and START DATE before Table.Group

Stéphane

[J4neX]

Thank you so much, you are the best ❤️

[J4neX]

Thank you for your answer. This solution is very similar to my problem, but I also have rows which END_DT first row is the same to START_DT next row and START_DT = END_DT in one row. For example:

SA_IDSTART_DTEND_DT

8702145434	2023-05-05	2023-05-08
8702145434	2023-05-08	2023-05-09
8702145434	2023-05-09	2023-05-11
8702145434	2023-05-12	2023-05-15
8702145434	2023-05-15	2023-05-15
8702145434	2023-05-19	2023-05-22
8702145434	2023-05-23	2023-05-23
8702145434	2023-05-26	2023-05-29
1132145497	2023-05-05	2023-05-08
1132145497	2023-05-08	2023-05-08
1132145497	2023-05-12	2023-05-15
1132145497	2023-05-16	2023-05-22
1132145497	2023-05-26	2023-05-29

[slorin]

Hi

I answered the same question 6 hours ago

https://community.fabric.microsoft.com/t5/Power-Query/Grouping-rows-by-min-start-data-and-max-end-da...

Stéphane