Forum Discussion
Solved
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. Curre...
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
