Replicate rows along all month from the first day

[Felipekard]

I has a table like

DATE	CAR	COLOR
01/01/2023	TESLA	BLACK
01/02/2023	MUSTANG	BLUE

I need replicate the rows for all the days of the month.

i want a table like:

DATE	CAR	COLOR
01/01/2023	TESLA	BLACK
02/01/2023	TESLA	BLACK
03/01/2023	TESLA	BLACK
04/01/2023	TESLA	BLACK

...

DATE	CAR	COLOR
31/01/2023	TESLA	BLACK
01/02/2023	MUSTANG	BLUE
02/02/2023	MUSTANG	BLUE
03/02/2023	MUSTANG	BLUE

...

DATE	CAR	COLOR
26/02/2023	MUSTANG	BLUE
27/02/2023	MUSTANG	BLUE
28/02/2023	MUSTANG	BLUE

[spinfuzer · Accepted solution]

If you do not have duplicate rows with the same DATE then you can skip the Table.Group step.  The Grouping is to avoid generating the same list multiple times which can slow down the query due to unnecessary repetition.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR2lENdgH0cg7eTj6OytFKsDkTaCSfuGBoc4+rmDFYS6KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, CAR = _t, COLOR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"CAR", type text}, {"COLOR", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DATE"}, {{"Count", each _, type table [DATE=nullable date, CAR=nullable text, COLOR=nullable text]}}),
    generate_dates = 
        Table.TransformColumns(
            #"Grouped Rows", 
                {
                    "DATE", 
                    each List.Dates(
                        _, 
                        Duration.TotalDays(Date.EndOfMonth(_) - _) + 1, 
                        #duration(1,0,0,0)
                    )
                }),
    #"Expanded DATE" = Table.ExpandListColumn(generate_dates, "DATE"),
    #"Expanded Count" = Table.ExpandTableColumn(#"Expanded DATE", "Count", {"CAR", "COLOR"}, {"CAR", "COLOR"})
in
    #"Expanded Count"