Custom Dimension Table in Desktop. Need help with code. All assitance appreciated

[Anonymous]

Hi all, 

I need to create a dimension table to build a relationship. The issue is very specific. Please see below. 

I have this list:

What I have

What I need: 

As you can see. It is the first list duplicated (12 times, one for each month). Then I need to add month, year up until 2025, and two columns with a code to be used to create a unique ID.. 

Could someone help me out with this?

Thank you! 🙂

Best regards

Morten

[Mariusz · Accepted solution]

Hi @Anonymous 

You can use the M code below, to create your table in Query Eritor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMxM7GwMFPSUTIEYiOlWJ1oJRNDYwszFzcUsYgQEzNTExQhN0c3oBC6qIWJhbkFQiQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Account = _t, Type1 = _t, Type2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "MonthNo", each {1..12}),
    #"Expanded Month" = Table.ExpandListColumn(#"Added Custom", "MonthNo"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Month", "Year", each {2019..2025}),
    #"Expanded Year" = Table.ExpandListColumn(#"Added Custom1", "Year"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Year", "Month", each Text.Start( Date.MonthName( #date([Year], [MonthNo], 1)), 3)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"MonthNo", Int64.Type}, {"Year", Int64.Type}, {"Month", type text}})
in
    #"Changed Type"

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Amazing speed and accuracy of your reply. Thanks a heap, worked like a charm 🙂