Not Able to Unpivot Nested Headers and Multiple Spreadsheets

[nangys]

Hello,

I have a file (attached) where each sheet has weekly data (and the user will keep adding as the weeks go by) and also has nested headers. I want to combine all of those in a tabular format.

I can do the code to unpivot one table (or one week/sheet), but not multiple sheets. Additionally, it needs to be able to "read" new sheets that will be added so it can unpivot those too without manual code.

Can you advise?

Thank you,

Fernando

 

[Dan Bliss]

nagys,

I am working on your problem, but have run into a bit of a snag.

I created a function that produces the result you are looking for when invoked using "Week1" or "Week2" as an argument, but the expression raises an error if used to iterate through a table of Week names.
"Unexpected error: Operation is not valid due to the current state of the object." Here a link to my request for help on TechNet.

Here is the M code for the function, fnWeek_Name. Function works if invoked using "Week 1" or "Week 2" or "Week 3" as a parameter:

AllWeeks is a separate query referenced by fnWeek_Name:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Dan\BlahBlah\Hub Truck Dashboard 2017 - Copy.xlsx"), null, true)
in
    Source

(Name as text) =>
let
    Source = AllWeeks,
    #"Week Sheet" = Source{[Item=Name,Kind="Sheet"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(#"Week Sheet",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1", 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 date}}),


    //Note (1)
    //  Save earliest date of the week, for use with Note(2) and Note (4).  
    #"Get FirstDateofWeek" = #"Changed Type"[Column6]{1},
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1", "Column2", "Column4", "Column5"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",1),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Transposed Table", "Column1", "Column1 - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1 - Copy", type date}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Column1 - Copy", null}}),


    //Note (2)
    //We need to keep track of dates, but can't use date as a column name because it changes each week..
    //  As a solution I convert the date to # of days difference (between 0 and 7),
    //  then once done transposing, add days-difference and FirstDayofWeek = return actual dates
    //  
    #"Calculated Days Dif" = Table.TransformColumns(#"Replaced Errors",{{"Column1 - Copy", each _ - #"Get FirstDateofWeek", type duration}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Calculated Days Dif",{{"Column1 - Copy", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type3", "Custom", each if [#"Column1 - Copy"] = null then [Column1] else [#"Column1 - Copy"] ),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Column1", "Column1 - Copy"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Column1"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Column1", type text}}),


    //Note (3)
    //Column1 must contain Column labels here so after transposition labels are in the first row, so columns must be reordered.
    //  The standard UI method for reordering all columns will not work because the list of columns can change each week.
    //  Since we never know ahead of time how many columns, we need to build the list of columns here.
    //  The next 5 expressions are used to create a list of current columns in the correct order.
    #"Get ColNames" = Table.ColumnNames(#"Changed Type2"),
    #"Converted to Table" = Table.FromList(#"Get ColNames", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Sorted Rows" = Table.Sort(#"Converted to Table",{{"Column1", Order.Ascending}}),
    #"Col List" = Table.ToList(#"Sorted Rows"),
    #"Reorder Columns" = Table.ReorderColumns(#"Changed Type2",#"Col List"),


    #"Transposed Table1" = Table.Transpose(#"Reorder Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"ORIGIN", "Carrier", "Name", "ETA", "Lock Out"}, "Attribute", "Value"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", Int64.Type}}),


    //Note (4)
    //Here is where Work Date is reconstructed.  Column "Attribute" stores Note (2)'s Calculated-Days-Dif.
    //  Add to each Calculated-Day-Dif Note(1)'s FirstDateofWeek 
    #"Change Attribute to Dates" = Table.TransformColumns(#"Changed Type4",{{"Attribute", each Date.AddDays(#"Get FirstDateofWeek", _)}}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Change Attribute to Dates",{{"Value", type time}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type5",{{"Value", "Actual Arrival Time"}, {"Attribute", "Work Date"}}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns1",{{"ETA", type time}, {"Lock Out", type time}, {"ORIGIN", type text}, {"Carrier", type text}, {"Name", type text}, {"Work Date", type date}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type6", {"Actual Arrival Time"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Actual Arrival Time] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "FirstDateofWeek", each #"Get FirstDateofWeek"),
    #"Changed Type7" = Table.TransformColumnTypes(#"Added Custom",{{"FirstDateofWeek", type date}})
in
    #"Changed Type7"

Here is the query that generates the error using fnWeek_Name

let    Source = AllWeeks,
    #"Removed Other Columns1" = Table.SelectColumns(Source,{"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns1", each ([Name] <> "Desired Result")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each fnWeek_Name([Name]))
in
    #"Added Custom"

I'll keep posting as answers are discovered.

 

[nangys]

Hi Dan,

Thanks for looking into this. I didn't expect it was going to be that complex!

Nangys

 

[Bill Szysz]

Hi nangys,

Have a look at the attachment file and tell me, is that what you want?
I used my function to unpivot multi level headers.
Look at the code (there are some notes )

cheers