Forum Discussion
Solved
Transform table script
Hello experts,
I have a statement table with thousands of rows and I need to build automation to transform it in the below way:
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
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.
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
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.
