Forum Discussion
Custom Dimension Table in Desktop. Need help with code. All assitance appreciated
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
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.
2 Replies
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.