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
Anonymous
Not applicable

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 haveWhat I have

 

 

 

 

 

 

What I need: 

What I need.pngAs 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

 

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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.


View solution in original post

2 REPLIES 2
Mariusz
Community Champion
Community Champion

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
Not applicable

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

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.