How to clean my data ?

HI hylosko ,

See if you are able to follow the video below:

https://youtu.be/pYH6o9ZWrjs

if not, please share the data or create a blank query, copy and paste the code below (written based on your screenshot (i.e. without data):

let

//Source path. Replace the blue text below with your source path
Source = Excel.Workbook(File.Contents("C:\Users\cktan\Documents\PQ Training\PQ Training - Multi Header (Dynamic).xlsx"), null, true),

//Replace the blue text below with your worksheet name

Worksheet = Source{[Item="Sales Report",Kind="Sheet"]}[Data],
#"Added Custom" = Table.AddColumn(Worksheet, "Create Record", each Record.ToList(_)),

//Added "Material" as a keyword to find the header row

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Header Row?", each List.ContainsAny([Create Record],{"Material"})),
#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 1, 1, Int64.Type),
HdrsRow = Table.SelectRows(#"Added Index", each ([#"Header Row?"] = true))[Index]{0},
FirstN = Table.FirstN(Worksheet,HdrsRow),
#"Transposed Table" = Table.Transpose(FirstN),
DefinedColumnTypes = Table.TransformColumnTypes(#"Transposed Table",List.Transform(Table.ColumnNames(#"Transposed Table"), each {_, type text})),
#"Filled Down" = Table.FillDown(DefinedColumnTypes,Table.ColumnNames(DefinedColumnTypes)),
Header = Table.Transpose(Table.FromList(List.Transform(Table.ToRows(#"Filled Down"), each Text.Combine(_,"|")))),
Body = Table.Skip(Worksheet,HdrsRow),
CombineTbls = Table.Combine({Header,Body}),
#"Promoted Headers" = Table.PromoteHeaders(CombineTbls, [PromoteAllScalars=true]),
#"Unpivoted Columns" = Table.Unpivot(#"Promoted Headers", List.Select(Table.ColumnNames(#"Promoted Headers"), each Text.Contains(_,"|")), "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.None)),
in
#"Split Column by Delimiter"

I leave the final step rename to you.

Let me know how it goes.

Regards

KT