Hi,
I have a large table...(which at the moment is only a static table) of historical data accross multiple year ends(sheet name "MASTER". Currently I'm exporting a report from accountancy software & copy/pasting data incrementally from export into MASTER . Each row is invoice data, 1 row per each invoice. I've now connected accountancy software to power query with ODBC.
This has always been the method but now Ive learnt power query (a little bit
) it would make sense to use power query. Especially as sometimes invoice data changes in accountancy system after I've exported my report.
However, if I convert MASTER into a query I dont wish prior year invoice details to refresh:
1. Might slow performance....only need to pull through last weeks data (amend every week triggered by reciept of Transport costing each week on email, I'll tie that in later).
2. *Main reason...accs system cant account for"COST PRICE" in context of date. Only current cost price, therefore, if refreshed all my historical invoice profitability figure would change as would recalculate inv item cost prices based upon current cost prices rather than what they were in 2019 etc. This one field is the only reason other than speed why I dont want the query to refresh historical.
I was going to leave my MASTER and create a new sheet which would pull in next incremantal invoice from invoice number parmater (which can ref from last invoice number from MASTER). Bring through new invoices to add then create a macro button to copy and paste these to the bottom of my historical sheet. But wanted to know if there was a better way?
Frequency (weekly) of these updates is triggered by receipt of an excel from Transport company. I need to do a lookup from this excel to my Historical so I can allocate Transport charges to each individual invoice in MASTER to give me a true net profit per invoice figure. Such transport charges arent recorded in the accountancy system. I have helper columns in the MASTER to lookup Transport charges.
Formatting Transport charges
Used to take a while as need to extract my lookup value which is 2 refs combined in the same cell. Therefore, I created a macro to do this. Not sure if could merge this Transport excel into an overall query and speed this process up as well.
Many Thanks
Gareth
I have a large table...(which at the moment is only a static table) of historical data accross multiple year ends(sheet name "MASTER". Currently I'm exporting a report from accountancy software & copy/pasting data incrementally from export into MASTER . Each row is invoice data, 1 row per each invoice. I've now connected accountancy software to power query with ODBC.
This has always been the method but now Ive learnt power query (a little bit
) it would make sense to use power query. Especially as sometimes invoice data changes in accountancy system after I've exported my report. However, if I convert MASTER into a query I dont wish prior year invoice details to refresh:
1. Might slow performance....only need to pull through last weeks data (amend every week triggered by reciept of Transport costing each week on email, I'll tie that in later).
2. *Main reason...accs system cant account for"COST PRICE" in context of date. Only current cost price, therefore, if refreshed all my historical invoice profitability figure would change as would recalculate inv item cost prices based upon current cost prices rather than what they were in 2019 etc. This one field is the only reason other than speed why I dont want the query to refresh historical.
I was going to leave my MASTER and create a new sheet which would pull in next incremantal invoice from invoice number parmater (which can ref from last invoice number from MASTER). Bring through new invoices to add then create a macro button to copy and paste these to the bottom of my historical sheet. But wanted to know if there was a better way?
Frequency (weekly) of these updates is triggered by receipt of an excel from Transport company. I need to do a lookup from this excel to my Historical so I can allocate Transport charges to each individual invoice in MASTER to give me a true net profit per invoice figure. Such transport charges arent recorded in the accountancy system. I have helper columns in the MASTER to lookup Transport charges.
Formatting Transport charges
Used to take a while as need to extract my lookup value which is 2 refs combined in the same cell. Therefore, I created a macro to do this. Not sure if could merge this Transport excel into an overall query and speed this process up as well.
Many Thanks
Gareth
