Need Help Transforming Data - Poorly Designed Database

[Katie-Farrand12]

Hi all,

I have a challenge that I cannot resolve. I am looking to transform data on a massive dataset that has been designed for convenience of data capturers rather than data analysts. 

My data has multiple days and about 40 different Description+Weight categories for each day. I have created a simple version of the dataset (see photos below). I would like to transform the multiple columns into 2 columns for each date.  I have put example pictures of the current format and the desired format. If you need more concrete example or actual text; pleaset let me know and I will quickly add it. 

Current Format

Current Data Format

Desired Format

Desired Data Format

I appreciate anyones help with this!

Warm regards,

Katie

[v-eqin-msft · Accepted solution]

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.

[v-eqin-msft]

Hi @Katie-Farrand12 ,

Any updates?

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[Ashish_Mathur · Accepted solution]

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"

[Katie-Farrand12]

Hi @Ashish_Mathur , thank you for the detail. I tried to follow your steps on the basic dataset so I can understand the logic. I am getting stuck on the 2nd last "Pivot" step, as per the below: 

#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged.2]), "Merged.2", "Value"),

 I keep getting error: 

I still dont quite understand how I got here; I was hoping to build the steps and then go through it to understand the logic. I'm not sure if I have done something wrong; but I am quite sure I followed the steps exactly as you laid them out. 

Do you have any advice?

Thanks,

Katie

[Ashish_Mathur]

You are welcome.  Under Transform, there is a Pivot column button.  Please check that.

[v-eqin-msft · Accepted solution]

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.

[HotChilli]

Is there any connection across a whole line? This is going to be lost in the desired result.

----

If there is none and you want to go ahead, you could make 2 copies of the table and remove the appropriate columns to make e.g. Table1 , Day column with 2 number 1 columns 

Table2 Day column with 2 number 2 columns

Table3 ........similar with 2 number 3 columns.

Rename the columns and then Append the 3 tables

[johnt75]

It would be a long query but you could union the different columns in SQL, e.g.

select day, unique_description_1 as unique_description, weight_1 as weight from table
union
select day, unique_description_2 as unique_description, weight_2 as weight from table
union
select day, unique_description_3 as unique_description, weight_3 as weight from table