Duplicate Rows & Add Dates

[mterry]

I have a data set with annual info, wondering if it's easy in Power Query to duplicate each row 11x and add a separate column with a corresponding month?

[Anonymous · Accepted solution]

Hi @mterry ,

Please try to create a new table using DAX:

New Table = 
var _date=FILTER(CALENDAR(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31)),DAY([Date])=1)
return CROSSJOIN('Table',_date)

Or 

New Table2 = 
var _date=SELECTCOLUMNS( GENERATESERIES(1,12,1),"Date",CONVERT( YEAR(TODAY())&"-"&[Value]&"-1",DATETIME))
return CROSSJOIN('Table',_date)

Output:

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

[Anonymous · Accepted solution]

Hi @mterry ,

Please try to create a new table using DAX:

New Table = 
var _date=FILTER(CALENDAR(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31)),DAY([Date])=1)
return CROSSJOIN('Table',_date)

Or 

New Table2 = 
var _date=SELECTCOLUMNS( GENERATESERIES(1,12,1),"Date",CONVERT( YEAR(TODAY())&"-"&[Value]&"-1",DATETIME))
return CROSSJOIN('Table',_date)

Output:

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

[amaronda]

How would the DAX change if trying to add dates for every day in the year, instead of every month of the year?

[mterry]

I had to make some tweaks, but your reply got me there using the date variable and CROSSJOIN, which I was not familiar with. Thanks

[VahidDM]

@mterry 

It sould be great if you share a sample of your data set here in a text format.

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[mterry]

I can't provide the actual data, but below is a sample of what I have, and what I would like it to look like:

Region	Thing	Amount
Asia	Thing1	1000
France	Thing2	5000
US	Thing3	2000

What I would like:

Region	Things	Amount	Date
Asia	Thing1	1000	1/1/2022
Asia	Thing1	1000	2/1/2022
Asia	Thing1	1000	3/1/2022
Asia	Thing1	1000	4/1/2022
Asia	Thing1	1000	5/1/2022
Asia	Thing1	1000	6/1/2022
Asia	Thing1	1000	7/1/2022
Asia	Thing1	1000	8/1/2022
Asia	Thing1	1000	9/1/2022
Asia	Thing1	1000	10/1/2022
Asia	Thing1	1000	11/1/2022
Asia	Thing1	1000	12/1/2022
France	Thing2	5000	1/1/2022
France	Thing2	5000	2/1/2022
France	Thing2	5000	3/1/2022
France	Thing2	5000	4/1/2022
France	Thing2	5000	5/1/2022
France	Thing2	5000	6/1/2022
France	Thing2	5000	7/1/2022
France	Thing2	5000	8/1/2022
France	Thing2	5000	9/1/2022
France	Thing2	5000	10/1/2022
France	Thing2	5000	11/1/2022
France	Thing2	5000	12/1/2022
US	Thing3	2000	1/1/2022
US	Thing3	2000	2/1/2022
US	Thing3	2000	3/1/2022
US	Thing3	2000	4/1/2022
US	Thing3	2000	5/1/2022
US	Thing3	2000	6/1/2022
US	Thing3	2000	7/1/2022
US	Thing3	2000	8/1/2022
US	Thing3	2000	9/1/2022
US	Thing3	2000	10/1/2022
US	Thing3	2000	11/1/2022
US	Thing3	2000	12/1/2022

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcizOTFTSUQrJyMxLNwQyDA0MDJRidaKV3IoS85JTYVJGQIYpTCo0GCZsDGQYgYVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Thing = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Thing", type text}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..12}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Date", each "1/"&Number.ToText([Custom])&"/2022"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom"})
in
    #"Removed Columns"

Hope this helps.

[mterry]

Thank you that did work

[Ashish_Mathur]

You are welcome.