Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 only for a table that is one year old, and since it is several years old, I do not know how to repeat the procedure.
Year | Product | Value |
2022 | A | 100 |
2022 | B | 1000 |
... | ... | ... |
2023 | A | 800 |
2023 | B | 500 |
... | ... | ... |
2025 | B | 300 |
... | ... | ... |
Result table:
Year | Month | Product | Value |
2022 | 1 | A | 100 |
2022 | 1 | B | 1000 |
... | ... | ... | ... |
2022 | 2 | A | 100 |
... | |||
2025 | 12 | B | 300 |
... | ... | ... | ... |
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks 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!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |