Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |