How to generate new lines of data by subcategories

[Ackbar-Learner]

Hi I need help to do the following. I have the below two tables. What i need is that each line of the first table to repeat itself X times at each change in transaction date but the second table department data should come as a new column in the first table for each line. (X being being the number of lines in the second table)

For example, the first line in first table with date 01/07/2021 should repeat six times and each of the 6 lines should contain 1 department in a new column named Department.

Thank you in advance

[AlienSx]

Hi, @Ackbar-Learner 

    size = Table.RowCount(Table2),
    rows = Table.ToRows(Table1),
    txform = 
        List.Transform(
            rows,
            (x) => 
                [a = List.Repeat({x}, size),
                b = Table.FromColumns(Table.ToColumns(Table.FromRows(a)) & {Table2[Department]}, Table.ColumnNames(Table1) & {"Department"})][b]
        ),
    z = Table.Combine(txform)

[Ackbar-Learner]

Hi @AlienSx 

Thanks for your reply. I am a bit new to this M power query code. I was like following the applied steps in power query so that I am more at ease understanding what is happening to the data. So I am not sure where to insert the code you provided 😞 

Kindly advise

Thanks

[AlienSx]

@Ackbar-Learner create new (blank) query, run Advanced Editor and replace everything inside with this

let
    Table1 = your_table1_name,
    Table2 = your_table2_name,
    size = Table.RowCount(Table2),
    rows = Table.ToRows(Table1),
    txform = 
        List.Transform(
            rows,
            (x) => 
                [a = List.Repeat({x}, size),
                b = Table.FromColumns(Table.ToColumns(Table.FromRows(a)) & {Table2[Department]}, Table.ColumnNames(Table1) & {"Department"})][b]
        ),
    z = Table.Combine(txform)
in
    z

don't forget to replace your_table1_name and your_table2_name (2nd and 3rd rows of code) with the names of your tables/queries.  

[AlienSx · Accepted solution]

or even simplier 

let
    Table1 = your_table1_name,
    Table2 = your_table2_name[Department],
    add_col = Table.AddColumn(Table1, "Department", each Table2),
    expand = Table.ExpandListColumn(add_col, "Department")
in
    expand

[Ackbar-Learner]

So easy in the end 🙂