cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Add table for each row in another table

I have two tables, Dates and Contracts:

 Date 01/01/2020 01/02/2020 01/03/2020

 Contracts CT1 CT2 CT3

How can I join them like this?

 Date Contracts 01/01/2020 CT1 01/01/2020 CT2 01/01/2020 CT3 01/02/2020 CT1 01/02/2020 CT2 01/02/2020 CT3 01/03/2020 CT1 01/03/2020 CT2 01/03/2020 CT3
3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion

@BlackBird

Try Cross Join function

CROSSJOIN(
VALUES(TABLE1[DATE]_,
VALUES(TABLE2[CONTRACTS)
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

Highlighted
Super User III

If you have a Dates and Contracts table as shown, in the Dates table, simply add a custom column in Power Query, then type =Contracts as your formula. It will initially look like this:

You an see the formula above just references the Contracts table. (if your table has a space in it, it would be ...each #"New Contracts")

Then just expand the Contracts column.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Community Support

Hi, @BlackBird

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

You may create a calculated table as below.

``````Table =
GENERATE(
'Date',
'Contracts'
)``````

Result:

Best Regards

Allan

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

3 REPLIES 3
Highlighted
Community Champion

@BlackBird

Try Cross Join function

CROSSJOIN(
VALUES(TABLE1[DATE]_,
VALUES(TABLE2[CONTRACTS)
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

Highlighted
Super User III

If you have a Dates and Contracts table as shown, in the Dates table, simply add a custom column in Power Query, then type =Contracts as your formula. It will initially look like this:

You an see the formula above just references the Contracts table. (if your table has a space in it, it would be ...each #"New Contracts")

Then just expand the Contracts column.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Community Support

Hi, @BlackBird

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

You may create a calculated table as below.

``````Table =
GENERATE(
'Date',
'Contracts'
)``````

Result:

Best Regards

Allan

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

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors