Forum Discussion
nicolasvc
Helper III
4 years agoAdd month column repeated by year using dax
I have a table in the sample the value of a product per year, from 2022 to 2025, and I would like to add the month column, in which it is repeated every month for every year. This can be done but onl...
- 4 years ago
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. You just need to add a custom column with {1..12} and then expand that to new rows.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlTSUXIEYkMDA6VYHbiQExAbIYSMoKqMUYVAqkxAQrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Product = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Product", type text}, {"Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each {1..12}), #"Expanded Month" = Table.ExpandListColumn(#"Added Custom", "Month"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Month",{{"Month", Int64.Type}}) in #"Changed Type1"Pat
mahoneypat
Microsoft Employee
4 years agoHere's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. You just need to add a custom column with {1..12} and then expand that to new rows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlTSUXIEYkMDA6VYHbiQExAbIYSMoKqMUYVAqkxAQrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Product = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Product", type text}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each {1..12}),
#"Expanded Month" = Table.ExpandListColumn(#"Added Custom", "Month"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Month",{{"Month", Int64.Type}})
in
#"Changed Type1"
Pat
nicolasvc
Helper III
4 years agoThanks for the help! I would have liked it in DAX, but I managed to change a few things to be able to use this solution in PQ!