Log In
Register
Home
Active Member
March 1, 2019
Offline
I am after some suggestions as to how one may be able to do this .
Start date End date (Aus date formatformat)
01/01/19 05/01/19
12/03/19 15/03/19
Using { Number.From([StartDate])..Number.From([EndDate]) } gives me a list
01/01/19
02/01/19
03/01/19
04/01/19
05/01/19
12/03/19
13/03/19
14/03/19
15/03/19
However if I have the one column list as the original data source how does one arrive to the 2 rows ( or indeed lots of rows) of start date end date? Thus in reality doing the transformation in reverse !
Have thought of grouping by min max with an index column possibly with modulo in combination but have not been able to transform it.
Any suggestions appreciated. Not necessarily looking for complex code - UI preferred as I'm not a coder/programmer but rather a data analyst .
Thanks in advance
SP
July 16, 2010
Offline
Hi Sylvain,
Welcome to our forum!
I'm not clear on what your desired result is and what data you have to work with? Are you trying to identify the first and last dates in a consecutive range or are you trying to generate a list of dates that have gaps in them?
Perhaps you could re-word your question identifying what you have to work with and what you're desired result is?
Thanks,
Mynda
Active Member
March 1, 2019
Offline
Hi Mynda,
Pleased to be a participant. I have attached a spreadsheet. Essentially I have start dates and dates that I can convert to a list of days. However I need to do the reverse. List of days transformed to start date and end date.
Indeed sometimes I need to prove (to someone else) that the transformation is correct. That is to do it in reverse of what I have done originally. Thanks for any suggestions!
Cheers SP
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Sylvain,
You can prove that without much pain... You can calculate the difference between all start-end dates, the total should be equal to the number of rows from expanded table, if they don't believe you, they can check row by row the entire table of results :).
It can be done, but you have to create a logical set of operations for that:
-Sort by ID and Dates
-Add an Index column
-add a new column to return the Index number where there is a change in ID or there is a gap between current row date and next row date
-Fill up
-Group By ID and the custom Column, extracting Min and Max dates for that group.
Source = Excel.CurrentWorkbook(){[Name="Originaldata"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"List days", type date}}),
#"Sorted Rows1" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending},{"List days", Order.Ascending} }),
#"Added Index" = Table.Buffer(Table.AddIndexColumn(#"Sorted Rows1", "Index", 0, 1)),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try (if [ID]<>#"Added Index"[ID]{[Index]+1} or Number.From([List days])<>Number.From(#"Added Index"[List days]{[Index]+1})-1 then [Index] else null) otherwise [Index]),
#"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
#"Grouped Rows" = Table.Group(#"Filled Up", {"Custom", "ID"}, {{"min", each List.Min([List days]), type date}, {"max", each List.Max([List days]), type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Custom"})
in
#"Removed Columns"
See the attached file.
Active Member
March 1, 2019
Offline
Hi Catalin, Thank you for your detailed response. Much appreciate the explanation of the theory behind this... The code is just the execution of this. In the end it is not complicated . I didn't have my "theory / process" right. Thus I will have to keep this in mind. Lesson learnt. Yes you are correct with the "double accrual accounting" check . Regardless I do know of someone who would indeed check 7000 lines by sight... Probably more ... Mmmm
Just to add that the 2 queries put together. . .from start date to end date becoming a list of days and then list days becoming rows of start date to end date ... would make the following happen if you ever needed to combine continuous periods as one:
Example being two rows ...
12/05/18 to 18/06/18
19/06/18 to 25/09/18
Thus the same as this one row ...
12/05/18 to 25/09/18
(There is bound to be more elegant simpler coding than this. )
One could create it as a function ...
Cheers
Sylvain
PS. .. I'll have to think of something more challenging
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
That's what the example I prepared is doing: takes a 1 column list of dates, then combines them into a "From:...To:..." dates range.
1 Guest(s)
