How to clean my data ?

[hylosko]

Hello experts!

Im new in PQ and im learning how to clean data

any ideas how i can clean my data to get this effect 

from this 

to this

[KT_Bsmart2gethe]

Hi @hylosko ,

Please see below the link to the solution with provided excel data:

solution 

Go to the advanced editor, and comments are available to help you understand the transformation.

Regards

KT

[KT_Bsmart2gethe · Accepted solution]

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

[hylosko]

Big thanks for me its excellent, but i have one question, it is possible to merge value 2020 and october (or other month) to get sth like this 2020-10 ? 

[KT_Bsmart2gethe]

You can merge 2020 and october and it will become "2020october" or "2020-october". This is a text column.

if you want 2020-10

go to step: added custom3 change "yyyyMM" to "yyyy-MM"

Table.AddColumn(#"Pivoted Column", "MONTHID", each Date.ToText(Date.FromText("1-"&[Attribute.3]&"-"&[Attribute.2]),"yyyy-MM"))

[hylosko]

I need your help with transform that code, I receive every month file which i storage in same folder, every file have the same format but months and years are changing (its month to month type of file). Do you have any ideas how i can combine that files ?

[hylosko]

Awesome, really big thanks for your help 🙂 

[hylosko]

https://www.transfernow.net/dl/20220609XxyhBFji 

[Greg_Deckler]

@hylosko Can you post that data in text? In general it looks unpleasant data to work with.

[hylosko]

I send in excel file