cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kschubert417 Frequent Visitor
Frequent Visitor

Repeat Date Values for Each Unique Part Number

Hi All,

 

My asks always seem to be relatively simple for the experts on this forum. My (hopefully simple) question can be displayed in the attached picture. What I am trying to do in the query editor is take the dates in my Date table and apply them to each unique part number in Table1 to get Table2 in the attached picture.

Capture.JPG

 

Is this possible? Thanks in Advance!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Repeat Date Values for Each Unique Part Number

Hi @kschubert417 

 

You seem to need a crossjoin of the two tables.  Check this out:

https://exceleratorbi.com.au/cross-join-with-power-query/

 

Super User
Super User

Re: Repeat Date Values for Each Unique Part Number

Hey, 

 

basically the answer is in a way simple, but also by no measn obvious :-)

 

Here are some screenshots how to expand the Calendar table with Part Number column from the Parts table.

 

Select the Calendar table and choose the command "Custom Column" from the Add Column menu:

And just type the name of the Parts table ...

image.png

It does not matter how you name the column, after you choose OK, the Calendar table should look like the:

image.png

 

just hit the "expand" icon, and the next dialog will look like this:

image.png

This dialog allows to select the columns you want to "select" from the Parts table.

Personally I prefer to deselect naming option, but this is my personal taste. After choosing "OK" you're done ...

image.png

 

You're done!

 

Please be aware that this is the simplest of all possible joins, creating a cartesian product of combinations. This often is quite useful, but be sure that you want what you get, meaning you should consider if the result is also valid if the rows in both tables are not unique ...

 

Regards,

Tom

 

 

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
3 REPLIES 3
Super User
Super User

Re: Repeat Date Values for Each Unique Part Number

Hi @kschubert417 

 

You seem to need a crossjoin of the two tables.  Check this out:

https://exceleratorbi.com.au/cross-join-with-power-query/

 

Super User
Super User

Re: Repeat Date Values for Each Unique Part Number

Hey, 

 

basically the answer is in a way simple, but also by no measn obvious :-)

 

Here are some screenshots how to expand the Calendar table with Part Number column from the Parts table.

 

Select the Calendar table and choose the command "Custom Column" from the Add Column menu:

And just type the name of the Parts table ...

image.png

It does not matter how you name the column, after you choose OK, the Calendar table should look like the:

image.png

 

just hit the "expand" icon, and the next dialog will look like this:

image.png

This dialog allows to select the columns you want to "select" from the Parts table.

Personally I prefer to deselect naming option, but this is my personal taste. After choosing "OK" you're done ...

image.png

 

You're done!

 

Please be aware that this is the simplest of all possible joins, creating a cartesian product of combinations. This often is quite useful, but be sure that you want what you get, meaning you should consider if the result is also valid if the rows in both tables are not unique ...

 

Regards,

Tom

 

 

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
kschubert417 Frequent Visitor
Frequent Visitor

Re: Repeat Date Values for Each Unique Part Number

This works perfect. Thank you very much.