Query from folder, problems with columns not lining up.

[Syndicate_Admin]

I have a Folder of which I recieve a new .xlsx file weekly. 

The documents always come to me with 5 rows of unneeded information before it gets to the column headers.  
I use the 'Date Created' from each file to track what date the rows of data came in.  This column, when the files are Expanded, ends up in the first column with the column heading of "Date created".

The 2 - 4 columns are unnecssary and are removed, as are the 6th and 7th columns.  The rest of the columns have the yyyyww number. (are not promoted to headers at that time).
Example: 

Date created	Column1	Column2	Column3	Column4	Column5	Column6
6/5/2023	Name	202322	202323	202324	202325	Total
6/5/2023	Alpha	5	8	4	10	27
6/5/2023	Beta	9	7	15	5	36

Two problems: 
1. If Now promote the first row to header then the query will always be looking for "6/5/2023".

2. When I add a new .xlsx file to the folder that the data is coming from the older column week info drops off and a new week is added to the end. I also lose the first column of data from the new files.
I need anything in a "Total" Column (Column6 above) to not be imported and I need the YYYYWW columns that are the same to line up underneith the previous set of weekly data, and not loose any columns of data from new files.
Example  

Date created	Column1	Column2	Column3	Column4	Column5	Column6
6/5/2023	Name	202322	202323	202324	202325	Total:
6/5/2023	Alpha	5	8	4	10	27
6/5/2023	Beta	9	7	15	5	36
6/10/2023	Name	202324	202325	202326	Total:	null
6/10/2023	Alpha	4	10	8	22	null
6/10/2023	Beta	15	5	11	31	null

[Syndicate_Admin]

to provide your code can make you get resolutions quickly

[Syndicate_Admin]

let
Source = Folder.Files("G:\ROOTDRIVE\DATAINPUT\CUSTOMER"),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date created", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date created", "Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (15)", each #"Transform File (15)"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Date created", "Transform File (15)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (15)", Table.ColumnNames(#"Transform File (15)"(#"Sample File (15)"))),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", type number}, {"Column13", type number}, {"Column14", type number}, {"Column15", type number}, {"Column16", type number}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", type text}, {"Column21", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Column3] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1", "Column2", "Column3", "Column4", "Column6", "Column7", "Column21"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"6/5/2023", type date}, {"Material #", type text}, {"202322", Int64.Type}, {"202323", type number}, {"202324", type number}, {"202325", type number}, {"202326", type number}, {"202327", type number}, {"202328", type number}, {"202329", type number}, {"202330", type number}, {"202331", type number}, {"202332", type number}, {"202333", type number}, {"202334", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"6/5/2023", "Date Created"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Date Created", "Material #"}, "Attribute", "Value"),
#"Trimmed Text1" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", Text.Trim, type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Trimmed Text1",{{"Material #", "CUST_PART_ID"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Attribute", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type3", {"Attribute"}, fDates, {"WeeknYear"}, "fDates", JoinKind.LeftOuter),
#"Expanded fDates" = Table.ExpandTableColumn(#"Merged Queries", "fDates", {"Start of Week"}, {"Start of Week"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded fDates"),
#"Rounded Up" = Table.TransformColumns(#"Removed Duplicates",{{"Value", Number.RoundUp, Int64.Type}}),
#"Calculated Start of Month" = Table.TransformColumns(#"Rounded Up",{{"Start of Week", Date.StartOfMonth, type date}}),
#"Renamed Columns2" = Table.RenameColumns(#"Calculated Start of Month",{{"Start of Week", "DATE"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns2",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Date Created", "DATE", "CUST_PART_ID"}, {{"QTY", each List.Sum([Value]), type number}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Grouped Rows",{{"QTY", Int64.Type}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type4",{{"CUST_PART_ID", Text.Trim, type text}}),
#"Merged Queries1" = Table.NestedJoin(#"Trimmed Text", {"CUST_PART_ID"}, CUST_PARTS, {"CUST_PART_ID"}, "CUST_PARTS", JoinKind.LeftOuter),
#"Expanded CUST_PARTS" = Table.ExpandTableColumn(#"Merged Queries1", "CUST_PARTS", {"PART_ID"}, {"PART_ID"}),
#"Added Custom" = Table.AddColumn(#"Expanded CUST_PARTS", "GROUP_ID", each "NAME", type text),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"GROUP_ID", "Date Created", "DATE", "CUST_PART_ID", "QTY", "PART_ID"})
in
#"Reordered Columns"

[Syndicate_Admin]

let
Source = Folder.Files("G:\ROOTDRIVE\DATAINPUT\CUSTOMER"),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date created", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date created", "Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (15)", each #"Transform File (15)"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Date created", "Transform File (15)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (15)", Table.ColumnNames(#"Transform File (15)"(#"Sample File (15)"))),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", type number}, {"Column13", type number}, {"Column14", type number}, {"Column15", type number}, {"Column16", type number}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", type text}, {"Column21", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Column3] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1", "Column2", "Column3", "Column4", "Column6", "Column7", "Column21"}),

Custom1=Table.Group(#"Removed Columns",Table.ColumnNames(#"Removed Columns"){1},{"n",each let a=Table.PromoteHeaders(_, [PromoteAllScalars=true]),b=Table.RenameColumns(a,{{Table.ColumnNames(a){0}, "Date Created"}}) in Table.Combine(Table.UnpivotOtherColumns(b,List.FirstN(Table.ColumnNames(b),2),"Attribute","Value")},0,(x,y)=>Byte.From(y="Material #"))[n])

in

Custom1

[Syndicate_Admin · Accepted solution]

I found that this tutorial is what I needed: https://www.youtube.com/watch?v=A5KxZhuwhv4