Need Help : Getting column data from one table to other with no relation between those tables,

[Anonymous]

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

[AlienSx · Accepted solution]

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

[dufoq3 · Accepted solution]

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_ID

[Anonymous]

HI @dufoq3, I tried the solution you have given, its working. both solutions given by you and @AlienSx are working. Thanks for your support.

[Anonymous]

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

[AlienSx]

@Anonymous replace #table function calls with references to your task and sprint tables accordingly. 

[AlienSx]

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

[Anonymous]

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

[AlienSx · Accepted solution]

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

[Anonymous]

Hi @AlienSx Thanks for your help. With referncecing I could able to do it..

also @dufoq3 thanks for your support.