SOLVED

Transform table script

Copper Contributor

Hello experts,

I have a statement table with thousands of rows and I need to build automation to transform it in the below way:

fabik_0-1707384803549.png

Structure of source is always same, D1-D2-I-I-I-I-SUM, and keeps repeating.

I need some automated script to run over thousands of rows and prepare output table in the way described above.

Can you please help me?

Thank you

4 Replies

@fabik I would use Power Query. You familiar with that? See attached.

 

Thank you Riny, seems like exactly what I need. I am not familiar with PQ, can you please give me some links or quick steps how to replicate your query to my workbook? Thank you
best response confirmed by fabik (Copper Contributor)
Solution

@fabik The site in the link below would be a good place  to start learning.

Introduction to Power Query - Excel Off The Grid

 

Here you can familiarize yourself with the basics of PQ. Once you have learned connect to a table and open the PQ Editor you will be able to follow the applied steps, which are all done by clicking in the user interface. No programming needed, although Excel will write the necessary M-code for you. Once you learn more about PQ, you can go into the code and modify it or write your own pieces of code from scratch.

 

The steps that enabled what you asked for are:

1) Connect to the data

2) Add an index column (0-based)

3) Add a modulo column that will allow you to identify each similar row in the groups of 7 (0-6)

4) Pivot the data

5) Fill up

6) Filter out rows no longer needed

7) Merge the four Info columns.

Clean up a bit.

 

In fact, step 7 required a small bit of code modification. When you merge the column, choose on of the standard delimiters. For example a colon.

Then you will see some code like this:

= Table.CombineColumns(#"Filtered Rows",{"2", "3", "4", "5"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Info")

 

Since you want a line feed replace ":" with "#(lf)". That's it.

 

 

 

 

Many thanks Riny, bit of struggle, but I managed and it works like a charm. It's perfect.
1 best response

Accepted Solutions
best response confirmed by fabik (Copper Contributor)
Solution

@fabik The site in the link below would be a good place  to start learning.

Introduction to Power Query - Excel Off The Grid

 

Here you can familiarize yourself with the basics of PQ. Once you have learned connect to a table and open the PQ Editor you will be able to follow the applied steps, which are all done by clicking in the user interface. No programming needed, although Excel will write the necessary M-code for you. Once you learn more about PQ, you can go into the code and modify it or write your own pieces of code from scratch.

 

The steps that enabled what you asked for are:

1) Connect to the data

2) Add an index column (0-based)

3) Add a modulo column that will allow you to identify each similar row in the groups of 7 (0-6)

4) Pivot the data

5) Fill up

6) Filter out rows no longer needed

7) Merge the four Info columns.

Clean up a bit.

 

In fact, step 7 required a small bit of code modification. When you merge the column, choose on of the standard delimiters. For example a colon.

Then you will see some code like this:

= Table.CombineColumns(#"Filtered Rows",{"2", "3", "4", "5"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Info")

 

Since you want a line feed replace ":" with "#(lf)". That's it.

 

 

 

 

View solution in original post