Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.