Forum Discussion

Felipekard's avatar
Felipekard
Frequent Visitor
2 years ago
Solved

Replicate rows along all month from the first day

I has a table like

DATECARCOLOR
01/01/2023TESLABLACK
01/02/2023MUSTANGBLUE

 

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

i want a table like:

 

DATECARCOLOR
01/01/2023TESLABLACK
02/01/2023TESLABLACK
03/01/2023TESLABLACK
04/01/2023TESLABLACK

...

DATECARCOLOR
31/01/2023TESLABLACK
01/02/2023MUSTANGBLUE
02/02/2023MUSTANGBLUE
03/02/2023MUSTANG

BLUE

...

DATECARCOLOR
26/02/2023MUSTANGBLUE
27/02/2023MUSTANGBLUE
28/02/2023MUSTANGBLUE
  •  

    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"

     

     

1 Reply

  •  

    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"