Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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
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
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
@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
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"
@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.
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.
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.
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 |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |