M / Power Query help for table merge (Conditional Cross Join)

[anandav]

Hi M experts,

I have a Tasks table with following data:

Task Name	Start	End
A	01/08/2018	03/08/2018
B	07/08/2018	17/08/2018
C	21/08/2018	31/08/2018

I have a Calendar table that spans 01/01/2018 to 31/12/2018.

I wanted to get one line per day for a given task between Start and End Date.

e.g.

Calendar Date	Task Name	Start	End
01/08/2018	A	01/08/2018	03/08/2018
02/08/2018	A	01/08/2018	03/08/2018
03/08/2018	A	01/08/2018	03/08/2018

I can do this in DAX by creating a new table with:

Table =

  FILTER(

      CROSSJOIN('Project Calendar', 'Project Tasks'),

      'Project Tasks'[Start] <= 'Project Calendar'[Date] &&

      'Project Tasks'[Finish] >= 'Project Calendar'[Date]

)

How can I achieve the same using M / Power Query.

I tried using Merge Queries but since I can only select one date field for Tasks table when merging with Calendar table, it does not give the desired results.

Any help will be greatly appreciated.

[anandav]

Hi @ImkeF,

Will you be able to assist on this question?

[ImkeF · Accepted solution]

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"

[quichejackson]

YouTube Curbal sent me here!

Thank you @ImkeF  for your elagant solution.

[anandav]

Hi @ImkeF,

WoW... that is an elegant solution!

Thanks a lot for you help. That worked perfectly.

Can you recommend any good recources to learn M / Power Query?

[ImkeF]

Hi @anandav, I've collected some learning resources here: https://www.thebiccountant.com/learning-resources/ 

[anandav]

Thanks a lot @ImkeF,

That's a good list of resources.

[Seward12533]

@ImkeF great list - I'd recommend Guy in a Cube's YouTube channel as well! 

[Seward12533]

@anandavI recommend https://www.amazon.com/Data-Monkey-Guide-Language-Excel/dp/1615470344

Don't worry about the Power Query for  Excel fool you.  Its all the same in PowerBI

[Anonymous]

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"

[anandav]

@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.

https://whatthetechisthat.wordpress.com/2018/08/03/transforming-a-data-range-into-separate-rows-using-power-query-and-dax/

And this blog explains how to do it in DAX.

[Anonymous]

Hi @anandav,

Thanks for the blog. Glod it could help you on this kind of case. 

[anandav]

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.

[anandav]

One option I found by Reeza Rad is :

http://radacad.com/dates-between-merge-join-in-power-query

Still open for any other solution using M. 

[Seward12533]

There may be some advanced M that will allow this (would not be surprised as it very flexible) but can you give a better idea of what your after and what results your trying to achive?  Also if you can buld the table in DAX why not use that? You can link those tables you create with DAX to other tables in your model - why does it have to be in M? 

Also you may be trying to build tables and force a solution that may not work well in PowerBI.  It really likes columnar date (tall narrow tables) vs Pivoted or Row based (wide) tables.  You may be better off unpivoting your data so you have something like the table below which could be related to your date table and then having dax meaures (letting filter context which check to see if the date is within the strart and end range of your project here is one possible example. You coudl of course attributes for Planned Start, Actual Start, Forecasted Start, Planned End, ...) and do all knds of calculations and could probably even do EVA if you wanted to go crazy.  

Status = 
VAR todaydate = TODAY() 
VAR DaysSinceStart = todaydate-CALCULATE(MIN(table[date]),Attribute="Start")
VAR DaysBeforeEnd = CALCULATE(MAX(table[date]),Attribute="END") - todaydate
RETURN
SWITCH(TRUE(),
  DaysSinceStart>0&&DaysBeforeEnd>0, "In Progress",
  DaysSinceStart<0, "Pending Start in "&-1*DaysSinceStart&" Days",
  DaysBeforeEnd <0, "Completed "&-1*DAysBeforeEnd&" Days Ago")

  

Task Name	Attribute	Date
A	Start	8/1/2018
A	End	8/3/2018
B	Start	8/7/2018
B	End	8/17/2018
C	Start	8/21/2018
C	End	8/31/2018