Splitting alternate rows into columns

[npust333]

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.

[dufoq3 · Accepted solution]

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

[npust333]

Thanks @dufoq3 that works!

[dufoq3]

You're welcome.