• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Append repeated blocks of column data into a single table

hi.prashant

New Member
I have this raw data that has repeating blocks of data which I need to append under the single head, as shown in the picture

76798

to

76799

Assume the raw data as one single fat table or range, as there are more than 90 blocks like that, I cant make separate tables/range for all the blocks
Thanks!
 

Attachments

  • ChandooForum1.xlsx
    12.5 KB · Views: 5
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"
 

Attachments

  • ChandooForum1.xlsx
    29.6 KB · Views: 4
Functions could be written to do this in 365 insider using Lambda functions.
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)<>"") )
 
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"

Thanks for the reply,
can we push the envelope a bit by not creating table/range for all the blocks as there are just too many.
and process the data as one fat table in PQ.
 
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)<>"") )

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 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.
This is still 'futures' in so far as the Lambda functions are still on beta release.
Given the necessary Excel version, the limitation of my formula is primarily the fact that constants are hard-wired into it, namely:
3 blocks of 10 rows, block width of 5 and additional spacing of 1.

There is currently a major challenge within the world of Excel in that 365 admits programming practices that are not remotely similar to traditional spreadsheets. Excel 365 itself is backward compatible but to exploit that I would have to rewrite every formula :(.
 

Attachments

  • Unstack Irregular blocks.xlsx
    15.2 KB · Views: 5
No. Because you need to have unique field names (headers) for each range/table. Power Query joins/appends unique tables which is what you are looking for. You may wish to use VBA to create each table. They would need to be spaced systematically, but it probably could be done. Then once each range/table has been created you could then append them.
 
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.
 

Attachments

  • Chandoo47090ChandooForum1.xlsm
    29.5 KB · Views: 5
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.
Thanks for the reply!
so far most practical solution, given the unavailability of Lamba and the size of data I have to process.
 
Back
Top