assign each date to each code

[Anonymous]

Hello all!

I need help whith the following query, i have two tables:

ID

CWF-XXXSE

CLehs-54616565

COeoe1-85121

CV41-584848516

COo3-132849632

CO3sd-fr81514

Week_Start_Date

29/01/2024

05/02/2024

12/02/2024

19/02/2024

26/02/2024

04/03/2024

11/03/2024

18/03/2024

25/03/2024

01/04/2024

08/04/2024

15/04/2024

I want to get to this : 

ID	Week_Start_Date
CWF-XXXSE	29/01/2024
CWF-XXXSE	05/02/2024
CWF-XXXSE	12/02/2024
CWF-XXXSE	19/02/2024
CWF-XXXSE	26/02/2024
CWF-XXXSE	04/03/2024
CWF-XXXSE	11/03/2024
CWF-XXXSE	18/03/2024
CWF-XXXSE	25/03/2024
CWF-XXXSE	01/04/2024
CWF-XXXSE	08/04/2024

What i need is to create rows for each ID with the full range of dates of the second table.

Any help would be consider!

[dufoq3 · Accepted solution]

Hi @Anonymous,

Result:

Create blank query, open it in Advanced editor end edit 1st and 2nd step. Delete whole code (as selected on the picture below) in these 2 steps and replace it with names of your tables

let
    Table_ID = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcqxCoAgFIXhd3HuDkfvvdgcNQUNQQniltEm5PtDgXKm/+PEaKZzoRDCPps0/LXmp5KwQkWl0ZZLBnmBRYODQeL5n0D7pziCs55HdbaTqxfdr4eATUof", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    Table_WeekStartDate = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7BCcAwDEPRXXwuWFbjksxisv8azc3R8cEXqMq4HOEEh+2nDOlgM6hcQn5CDMd7xaGcQqbwfDjz5hRGNvcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week_Start_Date = _t]),
    #"Added Custom" = Table.AddColumn(Table_ID, "Custom", each Table_WeekStartDate),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Week_Start_Date"}, {"Week_Start_Date"})
in
    #"Expanded Custom"

[v-junyant-msft · Accepted solution]

Hi @Anonymous ,

Have you solved your problem?
If not, please try this way:

Date[Week_Start_Date]

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel · Accepted solution]

=#table({"ID","Week_Start_Date"},List.TransformMany(Table1[ID],each Table2[Week_Start_Date],(x,y)=>{x,y}))

[v-junyant-msft · Accepted solution]

Hi @Anonymous ,

Have you solved your problem?
If not, please try this way:

Date[Week_Start_Date]

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dufoq3 · Accepted solution]

Hi @Anonymous,

Result:

Create blank query, open it in Advanced editor end edit 1st and 2nd step. Delete whole code (as selected on the picture below) in these 2 steps and replace it with names of your tables

let
    Table_ID = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcqxCoAgFIXhd3HuDkfvvdgcNQUNQQniltEm5PtDgXKm/+PEaKZzoRDCPps0/LXmp5KwQkWl0ZZLBnmBRYODQeL5n0D7pziCs55HdbaTqxfdr4eATUof", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    Table_WeekStartDate = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7BCcAwDEPRXXwuWFbjksxisv8azc3R8cEXqMq4HOEEh+2nDOlgM6hcQn5CDMd7xaGcQqbwfDjz5hRGNvcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week_Start_Date = _t]),
    #"Added Custom" = Table.AddColumn(Table_ID, "Custom", each Table_WeekStartDate),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Week_Start_Date"}, {"Week_Start_Date"})
in
    #"Expanded Custom"

[spinfuzer]

Add custom column =QueryName