Data modeling

[PBI_37]

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

[Alex87 · Accepted solution]

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!

[PBI_37]

Thank you very much @Alex87 

[HotChilli]

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.