Forum Discussion
M / Power Query help for table merge (Conditional Cross Join)
Sure: you add a column with list of those dates like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dew0DcyMLQAcYzhnFidaCUnkJA5kryhOYq8M1DICFm/MYITGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Task Name" = _t, Start = _t, End = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task Name", type text}, {"Start", type date}, {"End", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Start])..Number.From([End])}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}) in #"Changed Type1"
The following M code could give you the expected results. But I'm sure some one else could provide you with more efficient code. BTW: Why not use DAX?
let
Source = Excel.Workbook(File.Contents("C:\Users\jessica\Desktop\Book1.xlsx"), null, true),
Task_Sheet = Source{[Item="Task",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Task_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Task Name", type text}, {"Start", type date}, {"End", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Start"},Calendar,{"Date"},"Calendar",JoinKind.FullOuter),
#"Expanded Calendar" = Table.ExpandTableColumn(#"Merged Queries", "Calendar", {"Date"}, {"Calendar.Date"}),
#"Filled Down" = Table.FillDown(#"Expanded Calendar",{"Task Name", "Start", "End"}),
#"Added Conditional Column" = Table.AddColumn(#"Filled Down", "Remove", each if [End] < [Calendar.Date] then "No" else "Yes"),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Remove] = "Yes")),
#"Buffer Table" = Table.Buffer(#"Filtered Rows"),
#"Removed Columns" = Table.RemoveColumns(#"Buffer Table",{"Remove"})
in
#"Removed Columns"
Hi Anonymous,
It is a good solution and thanks a lot.
One propblem I hit is when the tasks have the same start and end date, then this will not work.
As you can see above the Design task has only one line.
I am rtying to adopt your logic with pivoting technique to see whether it will work.
Anonymous,
I used your solution to inspire a blog to solve a different problem.
Your solution could be used useful in situations where there is no overlap in Start and End data. e.g. seat bookings.
I used your suggestion to blog a solution for that problem with due credit to you.
And this blog explains how to do it in DAX.
