Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
J4neX
Frequent Visitor

Merge range dates in Power Query.

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
87021454342023-05-082023-05-09
87021454342023-05-092023-05-11
87021454342023-05-122023-05-15
87021454342023-05-192023-05-22
87021454342023-05-232023-05-23
87021454342023-05-262023-05-29
11321454972023-05-052023-05-08
11321454972023-05-122023-05-15
11321454972023-05-162023-05-22
11321454972023-05-262023-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
87021454342023-05-192023-05-23
87021454342023-05-262023-05-29
11321454972023-05-052023-05-08
11321454972023-05-122023-05-22
11321454972023-05-262023-05-29

 

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

 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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 

View solution in original post

8 REPLIES 8
slorin
Super User
Super User

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
Frequent Visitor

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)

 

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

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!

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
Frequent Visitor

Thank you so much, you are the best ❤️

J4neX
Frequent Visitor

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   
87021454342023-05-082023-05-09
87021454342023-05-092023-05-11
87021454342023-05-122023-05-15
87021454342023-05-152023-05-15
87021454342023-05-192023-05-22
87021454342023-05-232023-05-23
87021454342023-05-262023-05-29
11321454972023-05-052023-05-08
11321454972023-05-082023-05-08
11321454972023-05-122023-05-15
11321454972023-05-162023-05-22
11321454972023-05-262023-05-29
slorin
Super User
Super User

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.