Forum Discussion
Data modeling
Hi everyone
I have some questions about my data model.
I retrieve the data with this layout :
1) Can you confirm that it is necessary to merge in Power Query the Equipment_Type and Equipment_Name columns in order to have a table with only 3 columns?
2) Is it necessary to create two dimension tables (Equipment Type and Equipment Name)? or just one (Equipment Name with an Equipment Type column inside)?
PBI File :
https://drive.google.com/file/d/1k9hM21LY21gk_umZPBAetl2gL_q5UiCq/view?usp=sharing
Thank you for your help
Hello,
I reproduced your use case
1) I confirm, you need to clean the table and have only 3 columns. I added the M code to achieve this here below.
2) It it not necessary to have 2 dimension tables.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgjydwl1DlFwVNJRcg0M9QwwBDL8HH1dQTQCxeoglDrBhcEajKAajLAodUYxBKrBGKrBGEWpC/EOcCXggFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Equipment_Type1 = _t, Equipment_Name1 = _t, Equipment_Type2 = _t, Equipment_Name2 = _t, Equipment_Type3 = _t, Equipment_Name3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Split Column by Character Transition" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type", "Index"}), #"Removed Numeric" = Table.TransformColumns(#"Split Column by Character Transition", {"Type", each Text.Select(_, {"A".."Z","a".."z"}), type text}), #"Pivoted Column" = Table.Pivot(#"Removed Numeric", List.Distinct(#"Removed Numeric"[Type]), "Type", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column", {{"EquipmentType", "Equipment Type"}, {"EquipmentName", "Equipment Name"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"}) in #"Removed Columns"If it is answering your query, please mark my reply as the solution. Thanks!
3 Replies
1) Do you mean you are looking for a technique to get the table into 3 columns? Search the forum for Unpivot columns. You can chop the numbers off using splitcolumnbydelimiter and then pivot to get 3 or 4 columns. There will be lots of examples (in the Power Query forum)
If you want to do it by Appending tables instead : create 3 versions of the table, make each version have 3 columns by removing 2 unwanted columns, edit the column names to be the same, then Append 3 tables
2) It looks like one dimension table (i.e. one equip type = one equip name) should be good.
Hello,
I reproduced your use case
1) I confirm, you need to clean the table and have only 3 columns. I added the M code to achieve this here below.
2) It it not necessary to have 2 dimension tables.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgjydwl1DlFwVNJRcg0M9QwwBDL8HH1dQTQCxeoglDrBhcEajKAajLAodUYxBKrBGKrBGEWpC/EOcCXggFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Equipment_Type1 = _t, Equipment_Name1 = _t, Equipment_Type2 = _t, Equipment_Name2 = _t, Equipment_Type3 = _t, Equipment_Name3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Split Column by Character Transition" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type", "Index"}), #"Removed Numeric" = Table.TransformColumns(#"Split Column by Character Transition", {"Type", each Text.Select(_, {"A".."Z","a".."z"}), type text}), #"Pivoted Column" = Table.Pivot(#"Removed Numeric", List.Distinct(#"Removed Numeric"[Type]), "Type", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column", {{"EquipmentType", "Equipment Type"}, {"EquipmentName", "Equipment Name"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"}) in #"Removed Columns"If it is answering your query, please mark my reply as the solution. Thanks!
