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