Results 1 to 2 of 2

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

  1. #1
    Neophyte Inetguru's Avatar
    Join Date
    Nov 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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"}.

  2. #2
    Neophyte Inetguru's Avatar
    Join Date
    Nov 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •