Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

assign each date to each code

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 : 

 

IDWeek_Start_Date
CWF-XXXSE29/01/2024
CWF-XXXSE05/02/2024
CWF-XXXSE12/02/2024
CWF-XXXSE19/02/2024
CWF-XXXSE26/02/2024
CWF-XXXSE04/03/2024
CWF-XXXSE11/03/2024
CWF-XXXSE18/03/2024
CWF-XXXSE25/03/2024
CWF-XXXSE01/04/2024
CWF-XXXSE08/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!

3 ACCEPTED SOLUTIONS
dufoq3
Super User
Super User

Hi @Anonymous,

Result:

dufoq3_0-1706906286975.png

 

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

dufoq3_1-1706906454235.png

 

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"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Anonymous
Not applicable

Hi @Anonymous ,

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

Date[Week_Start_Date]

vjunyantmsft_0-1708410286967.png

vjunyantmsft_1-1708410376574.png

The final output is as below:

vjunyantmsft_2-1708410418245.png


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.

View solution in original post

wdx223_Daniel
Super User
Super User

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

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

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

Anonymous
Not applicable

Hi @Anonymous ,

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

Date[Week_Start_Date]

vjunyantmsft_0-1708410286967.png

vjunyantmsft_1-1708410376574.png

The final output is as below:

vjunyantmsft_2-1708410418245.png


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
Super User
Super User

Hi @Anonymous,

Result:

dufoq3_0-1706906286975.png

 

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

dufoq3_1-1706906454235.png

 

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"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

spinfuzer
Solution Sage
Solution Sage

Add custom column =QueryName

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.