Copy Date down until the next Date & Split Sets of Tables

[sa34techer]

Please help me find the right way to copy the date (Wednesday, February 28, 2024 in the picture) down until the next date, and so on.  Then split all sets of the top and bottom tables so can merge them.  

Power BI File 

Excel Data 

[dufoq3 · Accepted solution]

Hi @sa34techer,

You ask for merged tables but I thing you mean appended.

I tried to make it as dynamic as possible.

It is important that you musn't have headers promoted, so your data should be like this. If you have headers promoted, demote them first!

Result:

First table detail:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZfNbtswDIBfxcjZB0mWLGm3IMmwFK3tOQ6KoeihQwPstEOGHvr2E+k/0skhVVJgwAwkShjrIxmSos2np8UijXo9p4h+K/f1/Y+kqsu7zarZlsUu/LhrlnUTPjfFOqz7sgpr3WzC2pRb+L1+bLVcQFdIr+7gyqoIy/r7iROPh9ffhz+vL+9p8vXw8/j2cnxPlEsTJZQOl6X6IkR4JcsHkJig8rA6FRZlw5K3im+ukDg7JZguD/szpAVslAMewxCTjMio4MIbPEXHR3c/DPTG1uXDZtdsV8m2qPbNFNZMAKdNp862aq7EiSPOp8aZKWSoYLM+aq57w/ZIjgec7c9ZiWQA5JAmlfGERUG90W3RbOpiCcdoeT+Ej2mx7B+BHg1xk2P8bqKEOKVN6qyaUo6lEWItYVH4TXRpELEkTwQjPKvq/oCiirGSP0705pqy6aOWTFkppodI43F1JJvX8sQVmZlUG3sCStZIPAQ4HyJoWh1eRKM89nLSGzupQsmo7n+o/gxfAVGjnKCCcX3JYq0QmxHMmYZe0eaMgsX0YHPmrSICOdPOK9qduwhBLTjRF6kf8BiGmGSEZhESg8OeVUIEQgxygPmL9Y69MO/1xDNnmnZF+28bLz+QjuMxDDHJCMvcB1KjDnjoGLtNBEIMMsAxGmobD5j0rAoikDMNuKLttC17SAfWHsaGBCiGISYZMTSxVtLAZrorhjFEMQxtAZxgx1saCEwGNa8YHwWd668Va5XkSVQOdyvSeqIoYhZvU50uL6EEnIDgaCwC6cfjfPnOf2qgaH69HaeP//6Kx/9b65tUASXmceKTxgljU40P4R8cCwKX41Gex4kbjBMuJEFMqUuGAqnSLLcxJE/E/zpOiDDI5e4EvGQmsCIV7W1lHifmcaL7Oo8T8zgxjxPzOLH4rHHi+S8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t]),
    ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue, Table.ColumnNames(Source)),
    RemovedBlankColumns = Table.FromColumns(List.Select(Table.ToColumns(ReplacedValue), each List.NonNullCount(_) > 0)),
    RemovedBlankAndTotalRows = Table.SelectRows(RemovedBlankColumns, each 
     [ a = Record.ToList(_),
       b = List.NonNullCount(a) > 0 and not List.Contains(a, "total", Comparer.OrdinalIgnoreCase)
     ][b] ),
    ColumnNames = List.Buffer(Record.ToList(RemovedBlankAndTotalRows{0})),
    EachTableColumnNames = List.Buffer(
      [ a = List.PositionOf(ColumnNames, ColumnNames{0}, Occurrence.All), //1st column position of each table
        b = List.Zip( { List.Transform(a, each _ +1), List.RemoveFirstN(a, 1) & {List.Count(Table.ColumnNames(RemovedBlankAndTotalRows))} } ), //Position of each table columns (from to)
        c = List.TransformMany(b, (a)=> {{a{0}..a{1}}}, (x,y)=> List.Transform(y, each "Column" & Text.From(_)) )
      ][c] ),
    StepBack = RemovedBlankAndTotalRows,
    SeparatedTables = Table.FromList(List.Accumulate( 
      EachTableColumnNames,
      {},
      (s,c)=> s & { Table.PromoteHeaders(Table.SelectColumns(StepBack, c)) } ), Splitter.SplitByNothing(), type table[t=table]),
    Ad_FinalTable = Table.AddColumn(SeparatedTables, "FinalTable", each 
     [ a = Table.ColumnNames([t]){0}, //1st column name
       b = Table.Column([t], a), //1st column as list
       c = List.Transform( {-1} & List.PositionOf(b, a, Occurrence.All), (x)=> b{x+1} ), //Values we want fill down later
       d = Table.SelectRows([t], each not List.Contains(List.FirstN(Record.ToList(_), 1), a) ), //Filtered out header rows
       e = Table.ReplaceValue(d, null, null, (x,y,z)=> if List.Contains(c, x) then x else null, {a}),
       f = Table.FillDown(e, {a})
     ][f], type table),
    RemovedColumns = Table.RemoveColumns(Ad_FinalTable,{"t"})
in
    RemovedColumns