Transforming a General Ledger Data Structure with Power Query to a Flat Table for Power Pivot

Hello,

I have a general ledger import monthly in an excel format that needs to be transformed before I can use power pivot to do further data analysis.

The data imported for transformation looks like Table A in attached file.

The required data structure post  transformation is shown in Table B in attached file 

The steps of the process to transformation from A to B are explained as follows

1. I need to take the Consumer and Account name from Column 1 and copy it down to the left of the data for all transactions on that account before the next accounts data starts at Consumer2, at which point the same action needs to be looped until the next Consumer3 , etc
2. I need to take the Opening Balance and Date from Column 1 , and place the text ‘Opening Balance’ into the Column 3 for that specific row, and take the Date from Column 1 , being the last 10 characters, and place that date in a new column called ‘Date Column’. I also need to take the amount in Column 3 for this line item and place it in a new column ‘Opening Balance’. This need to be actioned per Consumer.
3. The same action in (2) above needs to be repeated for Closing Balance per Consumer.
4. I then need the Column 3 Descriptions to be pivot/unpivoted with their respective values, giving me a column name for that specific variable on a specific date. ( I understand that this would be best practice in order to analyse the data better with power pivot/DAX/power BI – is my understanding correct ?)
5. I then need to add a column that will first identify when a pmt was received , 22-Nov-2018 in the data below, and then populate the transactions that preceded that date with the account settlement date. This action is to happen whenever an account has a pmt received.
6. I then need a further column added to the data, which calculates the difference between the transactions date (as stated in the line items date column) and the settlement date (as populated by the actions of (5) above).
7. Lastly, I need a first column on the left of the data that index’s the data as a audit trail to every line item imported from the general ledger.

Please help. Many thanks.

Dean