Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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