cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
anandav Established Member
Established Member

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

Hi M experts,

 

I have a Tasks table with following data:

Task NameStartEnd
A01/08/201803/08/2018
B07/08/201817/08/2018
C21/08/201831/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 DateTask NameStartEnd
01/08/2018A01/08/201803/08/2018
02/08/2018A01/08/201803/08/2018
03/08/2018A01/08/201803/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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




13 REPLIES 13
Seward12533 New Contributor
New Contributor

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

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 NameAttributeDate
AStart8/1/2018
AEnd8/3/2018
BStart8/7/2018
BEnd8/17/2018
CStart8/21/2018
CEnd

8/31/2018

anandav Established Member
Established Member

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

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. 

Jessica_Seiya Established Member
Established Member

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

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"

2018-08-02_13-35-54.png

 

anandav Established Member
Established Member

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

Hi @Jessica_Seiya,

 

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.

 

Capture.JPG

 

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 Established Member
Established Member

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

@Jessica_Seiya,

 

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

 

Capture.JPG

 

And this blog explains how to do it in DAX.

anandav Established Member
Established Member

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

Hi @ImkeF,

Will you be able to assist on this question?

Super User
Super User

Re: 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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




anandav Established Member
Established Member

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

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?

Jessica_Seiya Established Member
Established Member

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

Hi @anandav,

 

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