Multiply two tables

[KervBruce]

The title of my question isn't clear but I didn't know what to call it.

I have a table of project, with 2 columns: ID and Title.

I have another table of reporting periods (months) from 2021 to today.

I want to create a table with a row for each project for each period.

So with 165 projects and 38 perios I expect 6270 rows.

Can you suggest the M Code to achieve this?

[ronrsnfld · Accepted solution]

• Add a custom column with a List of the Reporting Periods
• Expand that column to new rows.

Code might be something like:

   #"Added Custom" = Table.AddColumn(#"Previous Step", "Reporting Period", each #"Reporting Periods", type {text}),
    #"Expanded Reporting Period" = Table.ExpandListColumn(#"Added Custom", "Reporting Period")

If your List of Reporting periods is in a table named Reporting Periods, you might need to change the reference to a table column type reference, eg   #"Reporting Periods"[Column Name]

[ronrsnfld · Accepted solution]

• Add a custom column with a List of the Reporting Periods
• Expand that column to new rows.

Code might be something like:

   #"Added Custom" = Table.AddColumn(#"Previous Step", "Reporting Period", each #"Reporting Periods", type {text}),
    #"Expanded Reporting Period" = Table.ExpandListColumn(#"Added Custom", "Reporting Period")

If your List of Reporting periods is in a table named Reporting Periods, you might need to change the reference to a table column type reference, eg   #"Reporting Periods"[Column Name]

[KervBruce]

Thank you @ronrsnfld That was very simple 🙂

[ManuelBolz · Accepted solution]

Hello @KervBruce,

you only need to add two lines of code:

let
    Source = YourProjectTable,
    ColumnMonth = Table.AddColumn(Source, "Month", each YourDatesTable),
    OpenColumnMonth = Table.ExpandTableColumn(ColumnMonth, "Month", {"Month"}, {"Month"})
in
    OpenColumnMonth

 Best regards from Germany

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

[KervBruce]

 @ManuelBolz 

That's excelent, thank you