Beginner Needs Help: Can't Figure Out How to Transform "Dirty" Date Format Using UI Only

Hi - the first thing I did was make a slight tweek to the dirty data so that the headers become usable in Power Query for what you want. I filled in the blank Ship Modes then added a row that concatenates the ship mode and segment.

Once in Power Query, I did this:

No transformations

 

1. Promoted Headers

2. Change Type (Power BI did this by default - you can adjust if you want)

3. I selected the first two columns, then performed Unpivot Other Columns

4. Remove top rows - removed top 24 rows from above

5. Separated Attribute by delimiter of -

6. Now you can rename the columns to whatever you want.

 

let
Source = Excel.Workbook(File.Contents("C:\Users\audrey.gerred\Downloads\3.-Badly-Structured-Sales-Data-3.xlsx"), null, true),
#"Dirty 3_Sheet" = Source{[Item="Dirty 3",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Dirty 3_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type any}, {"First Class-Consumer", type any}, {"First Class-Corporate", type any}, {"First Class-Home Office", type any}, {"Same Day-Consumer", type any}, {"Same Day-Corporate", type any}, {"Same Day-Home Office", type any}, {"Second Class-Consumer", type any}, {"Second Class-Corporate", type any}, {"Second Class-Home Office", type any}, {"Standard Class-Consumer", type any}, {"Standard Class-Corporate", type any}, {"Standard Class-Home Office", type any}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1", "Column2"}, "Attribute", "Value"),
#"Removed Top Rows" = Table.Skip(#"Unpivoted Other Columns",24),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}})
in
#"Changed Type1"