Create a PQ function to complement the missing interval with the specified interval

I

Inetguru

New member
Joined
Nov 6, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2016
Please help me create a Power Query function.
I have a table (list of employees), for each row of which there is a list of date intervals (periods of work in different positions). There is a reference interval (a given year). It is necessary for each line to determine the list of missing intervals that are necessary to fill the reference interval. If the initial interval begins before the start of the reference interval, and ends after its start, then the initial interval at the initial interval must be adjusted by placing it at the beginning of the reference interval.
Similarly with the ending. If the initial interval ends after the end of the reference interval, then its end must be adjusted by limiting the date of the end of the reference interval. Next, for each interval (originally available and added), specify the attribute - "source" or "added." If the original interval intersected with the beginning or end of the reference interval and was adjusted, it is considered to be "source".
For example (MM.DD.YYYY):
1) reference interval {01/01/2019, 12/31/2019}.
2) a list of intervals filled for the line
{{02/20/2018, 01/31/2019},
{05/01/2019, 09/30/2019},
{10/01/2019, 02/09/2020}}
3) the following intervals should be obtained with their signs:
{{01/01/2019, 01/31/2019, "source"},
{02/01/2019, 04/30/2019, "added"},
{05/01/2019, 09/30/2019, "source"},
{10/01/2019, 12/31/2019, "source"}}

The first interval {02/20/2018, 01/31/2019} has been adjusted, because it begins on February 20, 2018, before the reference interval on January 1, 2019, and it turns out that on {01/01/2019, 01/31/2019, “source”}.
The second interval was added as missing.
The third interval without changes in emergency is supplemented by the sign of "source".
The fourth interval {10/01/2019, 02/09/2020} has been adjusted since it ends on 02/09/2020 after the end of the reference interval 12/31/2019 and it turns out {01/10/2019, 12/31/2019, "source"}.
 
At the heart of my solution is the List.Generate function, with which I created date ranges from date lists.
The function for creating intervals was created nested, because used twice
1st time - to create intervals from the source list of dates
2nd time - to create intervals from dates within a specified interval that are not in the original list
The result is obtained by combining 2 tables.
I called my function fn_AddMissingDatesToSpecifiedInterval_detail.
It has 4 arguments:
StartDate_input as date - start date of the interval within which the comparison is made. In my case, the beginning of the year.
EndDate_input as date - end date of the interval within which the comparison is made. In my case, the end of the year.
DatesList - a list of dates within the year that are in the original list,
dates_locale as text - the text string of the locale of the dates that are transferred in the list.
File with function

Code: 
(StartDate_input as date, EndDate_input as date, DatesList, dates_locale as text) =>
let


//this nested function create intervals from dates list
fn_DateGenerate =(InputData, Date_Type as text)=>
let
DateRangesGen = List.Generate(
    ()=> [Date=null, Counter=0], 
    each [Counter]<=List.Count(InputData), 
    each [
Date =
  let
    CurrentRowDate = InputData{[Counter]},
    PreviousRowDate = try InputData{[Counter]-1} otherwise null,
    NextRowDate = try InputData{[Counter]+1} otherwise null,
	
    MyDate = [Start_Date=
				(if PreviousRowDate = null then CurrentRowDate else
					if CurrentRowDate = Date.AddDays(Replacer.ReplaceValue(PreviousRowDate,null,0),1) then null else CurrentRowDate), 
             End_Date=(
						if NextRowDate = null then CurrentRowDate else
							if CurrentRowDate=Date.AddDays(Replacer.ReplaceValue(NextRowDate,null,0),-1) then null else CurrentRowDate)
			 ]
  in
MyDate,
Counter=[Counter]+1],
    each [Date]),
	sh1 = Table.FromList(DateRangesGen, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
	sh2 = Table.ExpandRecordColumn(sh1, "Column1", {"Start_Date", "End_Date"}, {"Start_Date", "End_Date"}),
	sh3 = Table.AddColumn(sh2, "Group", each "Group"),
	sh4 = Table.Group(sh3, "Group", {{"Start_Date", each List.RemoveNulls([Start_Date]), type anynonnull}, {"End_Date", each List.RemoveNulls([End_Date]), type anynonnull}}),
	sh5 = Table.AddColumn(sh4, "Tabled", each Table.FromColumns({[Start_Date],[End_Date]},{"Start_Date","End_Date"})),
	sh6 = Table.SelectColumns(sh5,{"Tabled"}),
	sh7 = Table.ExpandTableColumn(sh6, "Tabled", {"Start_Date", "End_Date"}, {"Start_Date", "End_Date"}),
	
DateRanges = Table.AddColumn(sh7,"Status",each Date_Type)
in
DateRanges,


//Dates from initial list
InputData_old = List.Transform(List.Sort(DatesList,Order.Ascending), each DateTime.Date(DateTime.From(_, dates_locale))),
DateRanges_old = fn_DateGenerate(InputData_old,"old"),


//the period within which it is necessary to supplement the missing intervals
StartDate  = StartDate_input,
EndDate = EndDate_input,
DaysInYear=Number.From(EndDate-StartDate)+1,
FullYearDatesList =List.Buffer(List.Dates(StartDate, DaysInYear, #duration(1, 0, 0, 0))),


//list of dates from missing intervals to be converted to intervals
DifferenceYearDatesList=List.Buffer(List.Sort(List.Difference(FullYearDatesList,InputData_old),Order.Ascending)),
DateRanges_new= fn_DateGenerate(DifferenceYearDatesList,"new"),


//combination of source and supplemented intervals 
DateRanges_old_new = Table.Combine({DateRanges_old, DateRanges_new}),
DateRanges_old_new_sorted = Table.Sort(DateRanges_old_new,{{"Start_Date", Order.Ascending}})
in
DateRanges_old_new_sorted
 
You must log in or register to reply here.
Back
Top