Log In
Register
Home
Active Member
October 22, 2021
Offline
Hi,
I have a report that is exported from a financial system that in some cases enters the data onto one row which is perfect.
But in some cases splits the data within a certain cell and then continues with the rest of the row data onto the next row.
Can power query merge the applicable cell and move the rest of the columns back up into the correct positioning on the row above.
See the example uploaded to illustrate.
I have highlighted rows 8 and 9 to show the first incorrect row. Rows 1-7 are perfect and is how the whole report should look before we even start the true transformations!!
please help currently I am copy-pasting this data into the previous row, my reports can be 50,000 rows long per month!
thanks
Sharon
July 16, 2010
Offline
Hi Sharon,
You can use Power Query to clean this data. You can use the Referencing next row in Power Query technique described under the heading "Referencing the Next Row in Power Query – Complex Table" towards the bottom of the tutorial, after option 3.
I've added a column for the Cashier ID in the query in the attached file as an example of one column being fixed. Once you've done them all, you can filter out the rows that have zero in the Tax Description column.
Hope that points you in the right direction. Let us know if you get stuck and share your file again to show where you're up to.
Mynda
1 Guest(s)
