Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have this table SALES containing postal codes from Belgium
I need to make aggregation totals per province and per region.
To do so I have this POSTAL CODES table from which must be decides in which province and in which region a postal code resides.
e.g. Postal code 4715 lays between 4000 and 4999 and so it's in the province of Liège and in the Region of Wallonie
I need to link the Sales table to Postal Codes table where 'Sales'[Postal Code] lays between 'Postal Codes'[Postal Code From] and 'Postal Codes'[Postal Code To]
How do I do this?
R.W.
Solved! Go to Solution.
With Power Query it is easy:
let // load of your table from your database, excel, csv, etc Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MDAAUUaWlkDKqai0uDg1pxjE9HBXitWJVjICyRpDFJmAFYUn5uTk58EZmalgdcYgBaYQdZZgdWE5iYm5xVBGXkpqUWoeWKUJUMgIYq0RRKVjXkl5alFBah6mYlOgkDFEsbEJbmNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Postal Code ID" = _t, #"Postal Code From" = _t, #"Postal Code To" = _t, Province = _t, Region = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Postal Code ID", Int64.Type}, {"Postal Code From", Int64.Type}, {"Postal Code To", Int64.Type}, {"Province", type text}, {"Region", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "PostalCodePrimaryKey", each List.Numbers( [Postal Code From], [Postal Code To] - [Postal Code From] + 1 ), type list ), #"Expanded PostalCodePrimaryKey" = Table.ExpandListColumn(#"Added Custom", "PostalCodePrimaryKey"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded PostalCodePrimaryKey",{{"PostalCodePrimaryKey", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Postal Code ID", "Province", "Region", "PostalCodePrimaryKey"}) in #"Removed Other Columns"
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hello @Anonymous
you need to make changes in your dimension table (postal codes) during the ETL process and repeat each row by the number of postal codes between START postdal code and END postal code, adding a column containing the sequencial postal code
which becomes the primary key that you shall use in the relationship
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo
Thanks for your reply.
How do I do this (repeat each row by the number of postal codes between Postal Code From and Postal Code To)?
Thanks,
R.W.
With Power Query it is easy:
let // load of your table from your database, excel, csv, etc Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MDAAUUaWlkDKqai0uDg1pxjE9HBXitWJVjICyRpDFJmAFYUn5uTk58EZmalgdcYgBaYQdZZgdWE5iYm5xVBGXkpqUWoeWKUJUMgIYq0RRKVjXkl5alFBah6mYlOgkDFEsbEJbmNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Postal Code ID" = _t, #"Postal Code From" = _t, #"Postal Code To" = _t, Province = _t, Region = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Postal Code ID", Int64.Type}, {"Postal Code From", Int64.Type}, {"Postal Code To", Int64.Type}, {"Province", type text}, {"Region", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "PostalCodePrimaryKey", each List.Numbers( [Postal Code From], [Postal Code To] - [Postal Code From] + 1 ), type list ), #"Expanded PostalCodePrimaryKey" = Table.ExpandListColumn(#"Added Custom", "PostalCodePrimaryKey"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded PostalCodePrimaryKey",{{"PostalCodePrimaryKey", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Postal Code ID", "Province", "Region", "PostalCodePrimaryKey"}) in #"Removed Other Columns"
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!