Forum Discussion
Splitting alternate rows into columns
Hello,
I currently have a task of cleaning a financial statement. The statement will come every month in PDF and I need to put it to an excel so that we can do further analysis and reporting.
Currently, I'm trying to use power query in excel to clean the data.
After several preprocessing, I arrive to this table format
| Column 1 | Price | Currency |
| Stock A | 50 | USD |
| North America | null | null |
| Stock B | 100 | GBP |
| UK | null | null |
As shown above, we have on column 1, an alternate row between stock name and its region, I want to split those alternate rows into each column, resulting in the below
| Column 1 | Column 2 | Price | CCY |
| Stock A | North America | 50 | USD |
| Stock B | UK | 100 | GBP |
Could you please help on this?
I tried to use the index column and modulo column trick to pivot the column, however, because there are other columns (e.g., price and ccy), the pivoting did not solve the issue
Thank you so much.
Hi npust333, try this.
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JT85WcFTSUTI1ABKhwS5KsTrRSn75RSUZCo65qUWZyYlA8bzSnBwYBZKHaHMCChkagPS5OwWAxUO90RXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, Price = _t, Currency = _t]), Transformed = [ a = List.Zip(List.Split(Source[Column 1], 2)), //Column1, Column2 b = Table.SelectRows(Table.SelectColumns(Source, List.Skip(Table.ColumnNames(Source))), (x)=> not List.Contains({"", "null", null}, x[Currency])), //Other columns c = Table.FromColumns( a & Table.ToColumns(b) ) //Merged to table ][c], RenamedColumns = Table.RenameColumns(Transformed,{{"Column3", "Price"}, {"Column4", "Currency"}}) in RenamedColumns
3 Replies
Hi npust333, try this.
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JT85WcFTSUTI1ABKhwS5KsTrRSn75RSUZCo65qUWZyYlA8bzSnBwYBZKHaHMCChkagPS5OwWAxUO90RXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, Price = _t, Currency = _t]), Transformed = [ a = List.Zip(List.Split(Source[Column 1], 2)), //Column1, Column2 b = Table.SelectRows(Table.SelectColumns(Source, List.Skip(Table.ColumnNames(Source))), (x)=> not List.Contains({"", "null", null}, x[Currency])), //Other columns c = Table.FromColumns( a & Table.ToColumns(b) ) //Merged to table ][c], RenamedColumns = Table.RenameColumns(Transformed,{{"Column3", "Price"}, {"Column4", "Currency"}}) in RenamedColumns
