…And here’s the verdict (drum roll please) yes it’s possible!
So from the point where we had split up our Table into Lists and Added the Index
I had to turn these Lists back to a Table, because there is no Fill down for type List
So here I’m checking if the first value from the first column of this Table is of type date
and when it is Fill Down the values in that Column - if it’s not just return the Table
finally by adding the column reference - extracting the first colum - turning it back into a List
followed by the other steps originally from post #2
All put together you’ll get this M code, so past that in and let me know if there are any problems
let
Source = Excel.Workbook(Parameter1, null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Removed Top Rows" = Table.Skip(Table1_Table,2),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Transposed Table" = Table.Transpose(#"Removed Blank Rows"),
#"Table To Columns" = Table.ToColumns(#"Transposed Table"),
#"Converted to Table" = Table.FromList(#"Table To Columns", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
#"ToTable step" = Table.Group(#"Added Index", {"Index"}, {{"ToTable", each Table.FromColumns(_[Column1]), type table [Column1=list, Index=number]}}, GroupKind.Local),
#"FillDownDates step" = Table.AddColumn(#"ToTable step", "FillDownDates", each if [ToTable][Column1]{0} is datetime or [ToTable][Column1]{0} is date then Table.FillDown([ToTable],{"Column1"})[Column1] else [ToTable][Column1]),
#"Added Index2" = Table.AddColumn(#"FillDownDates step", "Index2", each if [FillDownDates]{0} is datetime or [FillDownDates]{0} is date then [Index] else null),
#"Filled Down2" = Table.FillDown(#"Added Index2",{"Index2"}),
#"Grouped Rows" = Table.Group(#"Filled Down2", {"Index2"}, {{"Partition", each Table.FromColumns(_[FillDownDates]), type table [FillDownDates=list, Index2=number]}}, GroupKind.Local),
#"Combined Grouped Rows" = Table.Combine(#"Grouped Rows"[Partition]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Combined Grouped Rows", {"Column1", "Column2"}, "Attribute", "Value"),
#"Removed Other Columns1" = Table.SelectColumns(#"Unpivoted Other Columns",{"Column1", "Column2", "Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Column1", "Date"}, {"Column2", "In/Out"}, {"Value", "Name"}}),
#"Change Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"In/Out", type text}, {"Name", type text}})
in
#"Change Type"