Need Help Transforming Data - Poorly Designed Database

Hi Katie-Farrand12 ,

I'd suggest you do the transformation in Power Query. Below is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcuxDcAwCETRXa52EYINeBaL/dfIgRKl4EtI786BYEBuZZ2nc7GbZx5sIMdnZvVqZC83381/VPPVxvsvFDUSReYD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, #"Unique Description 1" = _t, #"Weight 1 (KG)" = _t, #"Unique Description 2" = _t, #"Weight 2 (KG)#(lf)" = _t, #"Unique Description 3" = _t, #"Weight 3 (KG)#(lf)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", Int64.Type}, {"Unique Description 1", Int64.Type}, {"Weight 1 (KG)", Int64.Type}, {"Unique Description 2", Int64.Type}, {"Weight 2 (KG)#(lf)", Int64.Type}, {"Unique Description 3", Int64.Type}, {"Weight 3 (KG)#(lf)", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Day"}, "Attribute", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Unpivoted Other Columns", "Unique Description", each if Text.Contains([Attribute], "Unique Description") then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Unique Description"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Weight", each if Text.Contains([Attribute],"Weight") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom2",{"Weight"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Attribute", "Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", Int64.Type}, {"Unique Description 1", Int64.Type}, {"Weight 1 (KG)", Int64.Type}, {"Unique Description 2", Int64.Type}, {"Weight 2 (KG)", Int64.Type}, {"Unique Description 3", Int64.Type}, {"Weight 3 (KG)", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Day"}, "Attribute", "Value"),
    #"Added Custom Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each let splitAttribute = Splitter.SplitTextByDelimiter(" (", QuoteStyle.None)([Attribute]) in Text.Reverse(Text.Middle(Text.Reverse(splitAttribute{0}?), 2)), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"Day", type text}}, "en-IN"),{"Day", "Custom"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index", "Index1"}, {"Value", "Index", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged.2]), "Merged.2", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns2",{{"Merged.1", Order.Ascending}, {"Unique Description", Order.Ascending}})
in
    #"Sorted Rows1"