Forum Discussion

KervBruce's avatar
KervBruce
Advocate I
2 years ago
Solved

Multiply two tables

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?

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago
    • 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]

     

  • ManuelBolz's avatar
    ManuelBolz
    2 years ago

    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.

4 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago
    • 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]

     

    • ManuelBolz's avatar
      ManuelBolz
      Responsive Resident
      2 years ago

      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.