Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 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
Solved! Go to 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.
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.
Amazing speed and accuracy of your reply. Thanks a heap, worked like a charm 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |