Forum Discussion
Helper IIINew row Values between end date and new start date
Hi guys;
I would like you can help me, I need to identify the DateDiff in days between end date and new start date in some assignation employee, so if the difference is >1 add the register for each day with difference with status Free.
Example:
Original Table:
My result must to be like this:
Is it possible add new row for each difference day like table above?
Regards and thanks for your time.
Hi RicLup ,
Please try to use the following custom column forumla, sample is in Query 4 and Query 5 of file:
let i = [IS], sd = [StartDate], ed = [EndDate], td = DateTime.Date(DateTimeZone.UtcNow()), OneDay = #duration(1,0,0,0), MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"], temp = Table.SelectRows(Assignment,each [IS] = i and [EndDate]>ed ), NextStartDay = if Table.RowCount(temp)=0 then null else Table.Min(temp,"StartDate")[#"StartDate"], Assigned = if [StartDate] = [EndDate] then Table.AddColumn(Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate"}),"unassignmentStatus",each "One Day Assignation") else Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}), Hisotoric = Table.AddColumn(Table.AddColumn( Table.RenameColumns( Table.FromList(List.Dates(sd+OneDay,Duration.Days(ed-sd),OneDay) , Splitter.SplitByNothing() , null, null, ExtraValues.Error) ,{"Column1","StartDate"}) ,"EndDate",each [StartDate]) ,"unassignmentStatus",each "Assigned Historic Register"), Pool = Table.AddColumn(Table.AddColumn( Table.RenameColumns( Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextStartDay-OneDay-ed),OneDay) , Splitter.SplitByNothing() , null, null, ExtraValues.Error) ,{{"Column1", "StartDate"}}) , "EndDate", each [StartDate]) , "unassignmentStatus", each "Difference End Date and New Start Date") in if sd = ed and ed < td and ed = MaxDay then Table.Combine({Assigned,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(td-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")}) else if sd = ed then Table.Combine({Assigned,Pool}) else if ed = MaxDay and ed < td then Table.Combine({Assigned,Hisotoric,Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(td-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{"Column1","StartDate"}),"EndDate",each [StartDate]),"unassignmentStatus",each "Last End Date Register until Today")}) else if NextStartDay = null or NextStartDay - ed = OneDay or NextStartDay < ed then Table.Combine({Assigned,Hisotoric}) else Table.Combine({Assigned,Hisotoric,Pool})
By the way, PBIX file as attached.
Best regards,
20 Replies
- Greg_Deckler
Community Champion
RicLup- ImkeF
Hi RicLup
this is a pattern you could use (please paste the code into the advanced editor and follow the stepsL):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSitWJVjIFsizALEMjINPQRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", Int64.Type}, {"End", Int64.Type}}), CreateListsOfExistingNumber = Table.AddColumn(#"Changed Type", "Custom", each {[Start]..[End]}), DetermineWhichNumbersAreMissing = List.Difference( {1..14}, List.Combine(CreateListsOfExistingNumber[Custom])), #"Converted to Table" = Table.FromList(DetermineWhichNumbersAreMissing, Splitter.SplitByNothing(), null, null, ExtraValues.Error), StartColumn = Table.RenameColumns(#"Converted to Table",{{"Column1", "Start"}}), EndColumn = Table.DuplicateColumn(StartColumn, "Start", "End"), AppendNewRowsToExitingRows = EndColumn & #"Changed Type", #"Sorted Rows" = Table.Sort(AppendNewRowsToExitingRows,{{"Start", Order.Ascending}}) in #"Sorted Rows"You should group you data on ID with "All Rows"-Operation and then apply this as a function to each resulting table.
v-lid-msftCommunity Support
Hi RicLup ,
We can create a custom column then expand it to meet your requirement:
1. create a custom column:
let i = [ID], sd = [Start Date], ed = [End Date], OneDay = #duration(1,0,0,0), MaxDay = Table.Max(Table.SelectRows(NameOfYourLastStep,each [ID] = i),"End Date")[#"End Date"], NextDay = if MaxDay <> ed then Table.Min(Table.SelectRows(NameOfYourLastStep,each [ID] = i and [Start Date]>ed),"Start Date")[#"Start Date"] else null, Assigned = Table.SelectColumns(Table.FromRecords({_}),{"Start Date","End Date","Status"}), Free =Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextDay-OneDay-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "Start Date"}}), "End Date", each [Start Date]), "Status", each "Free") in if NextDay = null or NextDay - ed = OneDay then Assigned else Table.Combine({Assigned,Free})2. remove orgin columns
3. expand the New column and do some modify
All the queries are here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE1VADGMDIwMdI10DU0RbCMDINuxuDgzPS81RSlWB7sOI7gOY11DInQAVSGxjTF0GMF0GMFUGUJcArXPjLAGI11z0mww0UViommIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, #"Start Date" = _t, #"End Date" = _t, Status = _t]), NameOfYourLastStep = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"ID", Int64.Type}}), #"Added Custom" = Table.AddColumn(NameOfYourLastStep, "New", each let i = [ID], sd = [Start Date], ed = [End Date], OneDay = #duration(1,0,0,0), MaxDay = Table.Max(Table.SelectRows(NameOfYourLastStep,each [ID] = i),"End Date")[#"End Date"], NextDay = if MaxDay <> ed then Table.Min(Table.SelectRows(NameOfYourLastStep,each [ID] = i and [Start Date]>ed),"Start Date")[#"Start Date"] else null, Assigned = Table.SelectColumns(Table.FromRecords({_}),{"Start Date","End Date","Status"}), Free =Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextDay-OneDay-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "Start Date"}}), "End Date", each [Start Date]), "Status", each "Free") in if NextDay = null or NextDay - ed = OneDay then Assigned else Table.Combine({Assigned,Free})), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Start Date", "End Date", "Status"}), #"Expanded New" = Table.ExpandTableColumn(#"Removed Columns", "New", {"Start Date", "End Date", "Status"}) in #"Expanded New"
By the way, PBIX file as attached.
Best regards,- RicLup
Hi v-lid-msft ,
The query is working, but in some rows in my model have an error when I create de Custom Column in the step one:
This is my code with one example with the error:
=let i = [IS], sd = [StartDate], ed = [EndDate], OneDay = #duration(1,0,0,0), MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"], NextDay = if MaxDay <> ed then Table.Min(Table.SelectRows(Assignment,each [IS] = i and [StartDate]>ed),"StartDate")[#"StartDate"] else null, Assigned = Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}), Pool =Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextDay-OneDay-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "StartDate"}}), "EndDate", each [StartDate]), "unassignmentStatus", each "Pool") in if NextDay = null or NextDay - ed = OneDay then Assigned else Table.Combine({Assigned,Pool})Thanks for your time.
Regards!
- v-lid-msft
Hi RicLup ,
If all the rows in screenshot has same value of IS, we can change the query in custom column as following:
let i = [IS], sd = [StartDate], ed = [EndDate], OneDay = #duration(1,0,0,0), MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"], temp = Table.SelectRows(Assignment,each [IS] = i and [StartDate]>ed), NextDay = if MaxDay <> ed and Table.RowCount(temp)>0 then Table.Min(temp,"StartDate")[#"StartDate"] else null, Assigned = Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}), Pool = Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextDay-OneDay-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "StartDate"}}), "EndDate", each [StartDate]), "unassignmentStatus", each "Pool") in if NextDay = null or NextDay - ed = OneDay then Assigned else Table.Combine({Assigned,Pool})All the query are here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE1VADGMDIwMdA11DY1hbCNdI0sg27G4ODM9LzVFKVYHmw4jXRMY01TX2JCwBmNdJCaJGoizwYQCDSa6xgaUaYgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IS = _t, Name = _t, StartDate = _t, EndDate = _t, unassignmentStatus = _t]), Assignment = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"IS", Int64.Type}}), #"Added Custom" = Table.AddColumn(Assignment, "New", each let i = [IS], sd = [StartDate], ed = [EndDate], OneDay = #duration(1,0,0,0), MaxDay = Table.Max(Table.SelectRows(Assignment,each [IS] = i),"EndDate")[#"EndDate"], temp = Table.SelectRows(Assignment,each [IS] = i and [StartDate]>ed), NextDay = if MaxDay <> ed and Table.RowCount(temp)>0 then Table.Min(temp,"StartDate")[#"StartDate"] else null, Assigned = Table.SelectColumns(Table.FromRecords({_}),{"StartDate","EndDate","unassignmentStatus"}), Pool = Table.AddColumn(Table.AddColumn(Table.RenameColumns(Table.FromList(List.Dates(ed+OneDay,Duration.Days(NextDay-OneDay-ed),OneDay), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "StartDate"}}), "EndDate", each [StartDate]), "unassignmentStatus", each "Pool") in if NextDay = null or NextDay - ed = OneDay then Assigned else Table.Combine({Assigned,Pool})), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDate", "EndDate", "unassignmentStatus"}), #"Expanded New" = Table.ExpandTableColumn(#"Removed Columns", "New", {"StartDate", "EndDate", "unassignmentStatus"}) in #"Expanded New"
By the way, PBIX file as attached.
Best regards,
- amitchandak
Super User
RicLup , You have create a new column
last Date = maxx(filter(Table,[ID]=earlier([ID])),[End date])
And now get the dates between this and start date. That you can done using a date calendar.
You have to create a new table
Something like this -
table2 = SUMMARIZE(filter(CROSSJOIN(Sheet1,'Date'),'Date'[Date]>=(Sheet1[last Date]) && 'Date'[Date]<=(Sheet1[Start Date])),Sheet1[ID],Sheet1[Name],table[Status],'Date'[Date],Sheet1[Start Date],Sheet1[End Date])There you need to handle the null value for last and change status when the last date is not null
You can refer the current employee code to create your new table.
- RicLup
Thanks amitchandak
With the new column the last date only get the earliest date for all dates by ID like this:
last Date = maxx(filter('Table',[ID]=EARLIER([ID])),[StartDate])May be I was thinking that the first step is, get by row the next start date and after count he difference days between End Date and next start date. So the expect table could looks like tthis:
In that way i could to do the series by day. Sorry if i don´t explain well or my idea it´s wrong.
Final Table:
Regards!
