Merging 3 tables with an attempt to pull data through to correct dates on main table

[wdgpw]

Hi all,

I have 3 Excel spreadsheets which I need to save down and merge on a monthly basis. The name of the file and their description is as follows..

'Who Holds' - Spreadsheet which contains the client holdings for one particular stock at the beginning of the month for the previous month. I will typically receive this file first week of the month so in the first week of Dec I would receive the data for the 1st Nov.

'Transaction Enquiry Security' - Spreadsheet which shows me any buy or sale of that one particular stock that has taken place over the previous month. Sometimes this will contain a client who has bought the stock for the first time mid month and therefore the clients name would not have appeared on the 'Who Holds' spreadsheet.

'0000734 SL3. 30.11.2022.xlsx' - This spreadsheet will have a column entitled 'Accrual Date' which basically lists each day of the previous month, the only other column is the one alongside it entitled 'Accrual Factor'. The date within the File Name will depend on what months data in contained within it.

The ultimate goal for me is have 'Accrual Date' on the far left column with the 'Accrual Factor' alongside it. I would then like each of the other column headers to be the client names and for each client it would show their holding for each day of the month.

The essence of my task is for each client I must take their stock holding for each day of the month and multiply it against the 'Accrual Factor'. Then take the sum for each client. In Excel I would normally use the =SUMPRODUCT function for each client.

The data contained within 'Who Holds' is data relating to the first day of the previous month therefore this data should be on the same line as 'Accrual Date' 01/11/2022 when referring to this example.

The data within 'Transaction Enquiry Security' references different dates throughout the month, not only am i trying to get the data for each transaction line on the same data line as the respective 'Accrual Date' I also want it to recognise whether the 'Transaction Type' column is a 'Buy' or a 'Sell'.

If we take Client 3 for example with a starting holding of 495,000 (taken from 'Who Holds') then has 2 Sells for 200.000 taking place over the month (taken from 'Transaction Enquiry Security'.

I am trying to get it to look like this for each client

I have managed to merge the files and it currently looks like this.. any help or suggestions would be greatly appreciated

My sincerest apologies if the above is long winded, this is my first time posting and I am very new to Power BI.

Thanks in advance, any questions please ask.

Kind regards

WDGPW