let
Source = Excel.CurrentWorkbook(),
#"Removed Columns" = Table.RemoveColumns(Source,{"Name"}),
#"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
#"Removed Top Rows" = Table.Skip(#"Expanded Content",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Column1] <> null)),
#"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers1", each ([FG] <> "FG"))
in
#"Filtered Rows1"
= LET(
Dataλ, LAMBDA(d,r,c,b,
INDEX(d, r, 6*(b-1)+c)),
k, SEQUENCE(30),
r, 1+MOD(k-1,10),
c, SEQUENCE(1,5),
b, 1+QUOTIENT(k-1,10),
array, Dataλ(data, r, c, b),
FILTER(array, INDEX(array,0,1)<>"") )
To do this easily, it is important to create a table/named range for each of the blocks. Then you can use the Mcode as follows as an example to append each to the other. This is created in Power Query. Look at the attached to analyze this code.
Code:let Source = Excel.CurrentWorkbook(), #"Removed Columns" = Table.RemoveColumns(Source,{"Name"}), #"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}), #"Removed Top Rows" = Table.Skip(#"Expanded Content",1), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Column1] <> null)), #"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers1", each ([FG] <> "FG")) in #"Filtered Rows1"
Functions could be written to do this in 365 insider using Lambda functions.
View attachment 76805
It could be a general purpose utility function but my implementation is specific to the limited data provided.
Code:= LET( Dataλ, LAMBDA(d,r,c,b, INDEX(d, r, 6*(b-1)+c)), k, SEQUENCE(30), r, 1+MOD(k-1,10), c, SEQUENCE(1,5), b, 1+QUOTIENT(k-1,10), array, Dataλ(data, r, c, b), FILTER(array, INDEX(array,0,1)<>"") )
This is still 'futures' in so far as the Lambda functions are still on beta release.Thanks Peter,
I really need to level up my excel skill to wrap my head around this bunch of code
will surely get back to you in few days after testing it on larger data set.
Thanks for the reply!In the attached is a button on the Output sheet near cell F1 which runs a macro that gives names to all the blocks of data on sheet Data.
For this to work well, each block of data on that sheet should be bound by completely blank rows and columns (or the edge of the sheet), and the headers should be the top row of each block. The headers should match in each block (if they don't, you'll just get extra headers in the output).
The blocks get named 'Blockn' where n is a sequenced number. It might do to delete manually all such named ranges in Name Manager before each run if you've made many changes to the blocks between runs.
The macro then moves on to refresh the table at cell H1 of the Output sheet.