Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nicolasvc
Helper III
Helper III

Add 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 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.

 

YearProductValue
2022A100
2022B1000
.........
2023A800
2023B500
.........
2025B300
.........

 

Result table:

 

YearMonthProductValue
20221A100
20221B1000
............
20222A100
...   
202512B300
............
1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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!

VahidDM
Super User
Super User

Hi @nicolasvc 

 

Is that value for 2025-12-B  in the result table correct?

 

Appreciate your Kudos!!

 

Hi @VahidDM! No, I already edited the table. Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.