Forum Discussion
Maha1
Helper II
Helper IIMerge tables where Date is between Start and End dates
I have a Master Table that include list of employees and I want to see who went on an assignment during the Date Period. I want to emphasize that not all employees went on assignment, which mean that not all employees are included in the Assignment Table and I don't want to filter them from the Master Sheet. Below is an example:
Master Table:
Assignment Table:
Master Table after merging should look like this:
Assignment:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ31DcyMDICMY1g7FgdoLShIYoYgm2sFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, startDate = _t, endDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"startDate", type date}, {"endDate", type date}}) in #"Changed Type"Master:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ31DcyADJjdRBCRphCxgghIyMjdFUwISRVhoaGKEKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Date Period" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Period", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Assignment,(k)=>k[Employee]=[Employee] and k[startDate]<=[Date Period] and k[endDate]>=[Date Period])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"startDate", "endDate"}, {"startDate", "endDate"}) in #"Expanded Custom"
3 Replies
- lbendlin
Super User
Assignment:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ31DcyMDICMY1g7FgdoLShIYoYgm2sFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, startDate = _t, endDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"startDate", type date}, {"endDate", type date}}) in #"Changed Type"Master:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ31DcyADJjdRBCRphCxgghIyMjdFUwISRVhoaGKEKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Date Period" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Period", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Assignment,(k)=>k[Employee]=[Employee] and k[startDate]<=[Date Period] and k[endDate]>=[Date Period])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"startDate", "endDate"}, {"startDate", "endDate"}) in #"Expanded Custom"
