Hi, this is not specific to the course content but I am loading bank statements from a PDF file in Power BI. The rows are unstructured and some of the rows needs to be combined (not based on a key or something similar).
For instance, on Page001 rows 2 and 3, rows 6 and 7, rows 8 and 9, rows 10 and 11, rows 12 and 13 and rows 14 and 15 needs to be combined. In Page002 there will even be more rows that will need to be combined. I cannot figure out a way to combine them with Group By as the rows are unstructured. To Transpose and then treat the rows as columns will be difficult to automate as the different Pages will have different amounts of rows to be combined.
Would appreciate if you can point me in the most appropriate direction. Prinsloo
Hi Prinsloo,
Welcome to the forum. Thanks for attaching your file. How would you identify which rows get combined? e.g. is it the rows that contain an amount of zero?
Can you please provide the desired result you'd like to see based on the sample file so that we can see the before and after views you're hoping to achieve?
Thanks,
Mynda
Thank you Mynda,
You are quite right. It seems as if it is the row which contains zero with the row above it. I have updated the file containing pages 001 to 003 with a before / current and after / desired result table on each sheet. Let me know if it doesn't make sense. I appreciate the help.
Thank you. Prinsloo
Hi Prinsloo,
It's kind of tricky because you need to reference the row below if the Amount column contains zero. In the attached file you'll see a custom column (4th step) has been added that checks if the amount on the row below is zero and then brings in the transaction description columns.
The rest is just rearranging the data to match your desired result layout.
Kind regards,
Mynda
Excellent! Thank you. That Custom Column formula is what I needed. It sure is going to help me a lot in future. Again thank you. Prinsloo
