Repeat Rows up to Month

[Jyaul1122]

Dear Friend,

i have two tables: Sales  Table.

Product Name	Sales
P1	10
P2	25

and month table:

Date	Month
1/31/2024	Jan-24
2/29/2024	Feb-24
3/31/2024	Mar-24
4/30/2024	Apr-24

i want to repeat all rows up to all month based on the month table..

Result table:

Product Name	Sales	Month
P1	10	Jan-24
P2	25	Jan-24
P1	10	Feb-24
P2	25	Feb-24
P1	10	Mar-24
P2	25	Mar-24
P1	10	Apr-24
P2	25	Apr-24

How can i achieve in Power BI

[Jyaul1122]

Thanks for your reply. Both reply are working fine.

[v-jialongy-msft]

Your solution is great, @Alex87  Here I have another idea in mind, and I would like to share it for reference.

Hi @Jyaul1122 

You can merge the two tables using the following dax:

ResultTable = 
CROSSJOIN(
    'Sales Table',
    'Month Table'
)

This is the result you want:

Best Regards,

Jayleny

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

[Alex87 · Accepted solution]

Hello @Jyaul1122 

You can do this in Power Query:

1. Add a custom column "Index" = 1 for both tables

2. Merge query as new 

3. Expand Month

SALES TABLE 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lEyNFCK1QGyjYBsI1Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Name" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Name", type text}, {"Sales", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Index", Int64.Type}})
in
    #"Changed Type1"

MONTH TABLE

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NtQ3MjAyUdJR8krM0wUyYnWilYz0jSxhwm6pSTBhYyTVvolFMGETfWMDmLBjAUQ4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Index", Int64.Type}})
in
    #"Changed Type1"

MERGE

let
    Source = Table.NestedJoin(#"Sales Table", {"Index"}, MonthTable, {"Index"}, "MonthTable", JoinKind.LeftOuter),
    #"Expanded MonthTable" = Table.ExpandTableColumn(Source, "MonthTable", {"Month"}, {"Month"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded MonthTable",{"Index"})
in
    #"Removed Columns"

If it answers your query, please mark my reply as the solution. Thanks!