Problem with combining files PBI

[hylosko]

Hi experts! and  @KT_Bsmart2gethe 

i have little problem with my pq

here is code:

//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"

The main problem is when i combining month files, when i combine 2 excel files with diffrent months :

1. first file 

2. second file

after combining in column 8 i have october and under i have january etc for others column, also in transposed table i have errors and it is not working. Can anyone help here ?

[KT_Bsmart2gethe]

Hi @hylosko,

If you combine files, you can use the import from folder method and apply the above transformation code. 

Regards

KT

[hylosko]

Yes i know, i used this option to combine files but after that i have errors in code which you send to me, i will prepare for you pbix with explaination and send you tommorow