Forum Discussion
Need Help : Getting column data from one table to other with no relation between those tables,
Hi,
I have two Tables
1. Task Table
2. Sprint Table,
I would like to bring Sprint_ID from Sprint table to Task Table as a new column as shown below based on the condition if Planned_Start_Date falls in to which Sprint Start & End Dates. And there is no relation between these two tables. Can anyone suggest how this can be acheived in power Query or DAX.
3. Result table
Can someone help me , how to get it.
Thanks
Srinivas
don't know what solution you are talking about. My solution works just fine. Next time post some data we can grab from your message, not images.
let task_table = #table( {"Task_ID", "Planned_Start_Date"}, {{"TS_1", #date(2023, 9, 20)}, {"TS_2", #date(2023, 9, 29)}, {"TS_3", #date(2023, 10, 20)}, {"TS_4", #date(2023, 10, 8)}} ), sprint_table = #table( {"Sprint_Start_Date", "Spint_End_Date", "Index", "Sprint_ID"}, {{#date(2023, 9, 18), #date(2023, 10, 1), 1, "S1"}, {#date(2023, 10, 2), #date(2023, 10, 15), 2, "S2"}, {#date(2023, 10, 16), #date(2023, 10, 29), 3, "S3"}} ), rename = Table.RenameColumns(task_table, {"Planned_Start_Date", "Sprint_Start_Date"}), combine = sprint_table & rename, sort = Table.Sort(combine,{{"Sprint_Start_Date", Order.Ascending}, {"Index", Order.Descending}}), fill_down = Table.FillDown(sort,{"Sprint_ID"}), filter = Table.SelectRows(fill_down, each ([Task_ID] <> null))[[Task_ID], [Sprint_Start_Date], [Sprint_ID]], z = Table.RenameColumns(filter, {"Sprint_Start_Date", "Planned_Start_Date"}) in zHi Anonymous,
Result:1.) Delete whole code in 1st step task_table step and replace it with your task_table reference
2.) do the same for 2nd step sprint_table
let task_table = #table( {"Task_ID", "Planned_Start_Date"}, {{"TS_1", #date(2023, 9, 20)}, {"TS_2", #date(2023, 9, 29)}, {"TS_3", #date(2023, 10, 20)}, {"TS_4", #date(2023, 10, 8)}} ), sprint_table = #table( {"Sprint_Start_Date", "Sprint_End_Date", "Index", "Sprint_ID"}, {{#date(2023, 9, 18), #date(2023, 10, 1), 1, "S1"}, {#date(2023, 10, 2), #date(2023, 10, 15), 2, "S2"}, {#date(2023, 10, 16), #date(2023, 10, 29), 3, "S3"}, {#date(2023, 10, 30), #date(2023, 11, 12), 4, "S4"}, {#date(2023, 11, 13), #date(2023, 11, 26), 5, "S5"}, {#date(2023, 11, 27), #date(2023, 11, 10), 6, "S6"}}), Ad_Sprint_ID = Table.AddColumn(task_table, "Sprint_ID", each Table.SelectRows(sprint_table, (x)=> x[Sprint_Start_Date] <= [Planned_Start_Date] and x[Sprint_End_Date] >= [Planned_Start_Date]){0}[Sprint_ID]) in Ad_Sprint_ID
8 Replies
hi, Anonymous
1. rename Planned_Start_Date to Sprint_Start_Date
2. combine tables together
3. sort by Sprint_Start_Date (order ascending) and Index (descending)
4. Fill down Sprint_ID column
5. Filter Task_ID <> null
HI. Thanks AlienSx
for your response, but this solution is giving only matching rows from both tables, where Planned_Start_Date = Sprint_Start_Date,, but few Planned_Start_Date (Sprint_Start_Date) falls in between Sprint_Start_Date & Sprint_End_Date. I am not getting Sprint_ID for those rows. Can you give some eloborated solution, I am new to Power BI .Thanks
Srinivas
don't know what solution you are talking about. My solution works just fine. Next time post some data we can grab from your message, not images.
let task_table = #table( {"Task_ID", "Planned_Start_Date"}, {{"TS_1", #date(2023, 9, 20)}, {"TS_2", #date(2023, 9, 29)}, {"TS_3", #date(2023, 10, 20)}, {"TS_4", #date(2023, 10, 8)}} ), sprint_table = #table( {"Sprint_Start_Date", "Spint_End_Date", "Index", "Sprint_ID"}, {{#date(2023, 9, 18), #date(2023, 10, 1), 1, "S1"}, {#date(2023, 10, 2), #date(2023, 10, 15), 2, "S2"}, {#date(2023, 10, 16), #date(2023, 10, 29), 3, "S3"}} ), rename = Table.RenameColumns(task_table, {"Planned_Start_Date", "Sprint_Start_Date"}), combine = sprint_table & rename, sort = Table.Sort(combine,{{"Sprint_Start_Date", Order.Ascending}, {"Index", Order.Descending}}), fill_down = Table.FillDown(sort,{"Sprint_ID"}), filter = Table.SelectRows(fill_down, each ([Task_ID] <> null))[[Task_ID], [Sprint_Start_Date], [Sprint_ID]], z = Table.RenameColumns(filter, {"Sprint_Start_Date", "Planned_Start_Date"}) in z
Hi Anonymous,
Result:1.) Delete whole code in 1st step task_table step and replace it with your task_table reference
2.) do the same for 2nd step sprint_table
let task_table = #table( {"Task_ID", "Planned_Start_Date"}, {{"TS_1", #date(2023, 9, 20)}, {"TS_2", #date(2023, 9, 29)}, {"TS_3", #date(2023, 10, 20)}, {"TS_4", #date(2023, 10, 8)}} ), sprint_table = #table( {"Sprint_Start_Date", "Sprint_End_Date", "Index", "Sprint_ID"}, {{#date(2023, 9, 18), #date(2023, 10, 1), 1, "S1"}, {#date(2023, 10, 2), #date(2023, 10, 15), 2, "S2"}, {#date(2023, 10, 16), #date(2023, 10, 29), 3, "S3"}, {#date(2023, 10, 30), #date(2023, 11, 12), 4, "S4"}, {#date(2023, 11, 13), #date(2023, 11, 26), 5, "S5"}, {#date(2023, 11, 27), #date(2023, 11, 10), 6, "S6"}}), Ad_Sprint_ID = Table.AddColumn(task_table, "Sprint_ID", each Table.SelectRows(sprint_table, (x)=> x[Sprint_Start_Date] <= [Planned_Start_Date] and x[Sprint_End_Date] >= [Planned_Start_Date]){0}[Sprint_ID]) in Ad_Sprint_IDHi dufoq3 @AlienSx , Thanks for your response.
in My requirement, the task_table would be dynamically growing as the time progress as well Sprint_table, let's say tasks will keep increasing (data in Task_table is fetched from excel ) like TS_20.....TS_30....TS_35 etc...and the these tasks will fall into subsequent sprints (between Sprint_Start_Date & Sprint_End_Date) based on their Planned_Start_dates. for other project(s)..the sprint_table would containg different start & end dates.I am looking for a generalized solution which can be used across projects. I understood from your solution that, it works for a fixed number of rows in task_table. Hope my understanding is right and put my requirement rightly..
Thanks
Srinivas
Anonymous replace #table function calls with references to your task and sprint tables accordingly.