Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
anandav
Skilled Sharer
Skilled Sharer

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

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

14 REPLIES 14
anandav
Skilled Sharer
Skilled Sharer

Hi @ImkeF,

Will you be able to assist on this question?

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

YouTube Curbal sent me here!

 

Thank you @ImkeF  for your elagant solution.

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?

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks a lot @ImkeF,

That's a good list of resources.

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

@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
Not applicable

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

 

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

 

Capture.JPG

 

And this blog explains how to do it in DAX.

Anonymous
Not applicable

Hi @anandav,

 

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

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.

 

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
Skilled Sharer
Skilled Sharer

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
Solution Sage
Solution Sage

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.