Power Query - Summarize and consolidate Daily files

[choop]

Hi,

Here is my challenge:

I have a daily report sales projection report with hundreds of line items vertically, while horizontally breaking those sales into 12 rolling weeks in the future (of course the date isn't formatted super nicely (ie. 8/10), and the weeks change as you look forward or back in time). I need to summarize the total of all line items all into one single row per daily file - I don't care about customer or any other field - the output columns should be:
Date, Week 1$, Week 2$, ... Week 12$.

The first row of the file contains the date (among other stuff like the report name). I can't use the date created/modified field to derive date as the files are copied by a macro and don't match the real date.

I am able to get what I need from one file at a time, by promoting headers, pivoting, unpivoting, etc. The problem is that since the date and weeks at the top are constantly changing, my query becomes to hardcoded with column names for it to work with the rest of the files. Also, the files have a different tab name (with the date) for each file - I was able to fix this by choosing "= Source{0}[Data]" as my data source (ie. the first worksheet).

I am fine with naming the weekly columns as generic headers (1,2,3,....12), and using a lookup table to match it to the appropriate week based on the date that I strip from row 1 and put into column 1.

I'm sure this is kind of confusing - happy to explain further if I can make it any clearer. My transform query and a snip of the report are below:

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"Weekly Back log Dollars 2020-07_Sheet" = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Weekly Back log Dollars 2020-07_Sheet", [PromoteAllScalars=true]),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Date:  7/13/2020                                 Weekly Backlog in Dollars                                          Time:   6:01:24", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date:  7/13/2020                                 Weekly Backlog in Dollars                                          Time:   6:01:24", "Date:  7/13/2020                                 Weekly Backlog in Dollars      "),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Date:  7/13/2020                                 Weekly Backlog in Dollars      ", "Date:  7/13/2020                                 Weekly Backlog in Dollars                                          Time:   6:01:24", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Reordered Columns", {"Date:  7/13/2020                                 Weekly Backlog in Dollars      "}, "Attribute", "Value"),
    #"Split Column by Positions" = Table.SplitColumn(#"Unpivoted Only Selected Columns", "Attribute", Splitter.SplitTextByPositions({7, 17}), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Attribute.1", type date}, {"Attribute.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Attribute.1", "Date:  7/13/2020                                 Weekly Backlog in Dollars                                          Time:   6:01:24", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Attribute.1", "DATE"}, {"Date:  7/13/2020                                 Weekly Backlog in Dollars                                          Time:   6:01:24", "Cust"}, {"Column2", "Dest"}, {"Column3", "Myr"}, {"Column4", "PA"}, {"Column5", "Cust Part"}, {"Column6", "Late"}, {"Column7", "#1"}, {"Column8", "#2"}, {"Column9", "#3"}, {"Column10", "#4"}, {"Column11", "#5"}, {"Column12", "#6"}, {"Column13", "#7"}, {"Column14", "#8"}, {"Column15", "#9"}, {"Column16", "#10"}, {"Column17", "#11"}, {"Column18", "#12"}, {"Column19", "Total"}, {"Column20", "Book"}, {"Column21", "BU"}, {"Column22", "Plant"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Value", "Cust", "Dest", "Myr", "PA", "Cust Part"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "#1 Less Late", each [#"#1"]-[Late]),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom",{"DATE", "Late", "#1 Less Late", "#1", "#2", "#3", "#4", "#5", "#6", "#7", "#8", "#9", "#10", "#11", "#12", "Total", "Book", "BU", "Plant"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns2",{"Late", "#1", "Total", "Book", "BU", "Plant"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns2",1),
    #"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Removed Top Rows", {"#1 Less Late", "#2", "#3", "#4", "#5", "#6", "#7", "#8", "#9", "#10", "#11", "#12"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Only Selected Columns1", List.Distinct(#"Unpivoted Only Selected Columns1"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

Thanks in advance!
Chris

 

[choop]

I think I solved it! Turns out the file names also include the date, so I can capture the date from the regular query (not the transform section), and thus ignore all of the header information that was causing hardcoding issues.