Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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.
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.
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.
How would the DAX change if trying to add dates for every day in the year, instead of every month of the year?
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
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/
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 |
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.
Thank you that did work
You are welcome.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |