With Power Query everything is easier | Xyclos
Updated: Apr 23
Case to solve
The other day a client called me who had the following problem.
On the one hand, it has a book currently made up of 3 tabs with the sales record per month per invoice: January, February and March 2024. We will call this book Invoices .
On the other hand, you have a workbook that has only one sheet that you downloaded from the ERP that contains the Journal workbook. We will call this book Journal.
The Invoices book has a record for each sales invoice.
The Journal book has several records for each invoice, for example, it has one record for recording VAT withholding, another record for withholding at source, and another record for when the invoice was paid.
The client requires adding in Invoices the value of the IR (Income Tax) and the value of the VAT that is taken from the Journal book.
She does this monthly manually:
Open both files
In the Invoices file, the sheet of the month to be processed goes where it identifies an invoice
In the Journal file, look for the VAT withholding value of the invoice to work with
Copy the value and paste it into a new column: Ret. VAT in the Invoices book
It does the same with the value of the IR (Income Tax) withholding in a column called: Ret. IR.
And as she told me, he does it monthly with around 500 invoice records.
She also tells me that it is a process that takes a lot of time, with which I indicate that I agree and that it is also very risky to work in this way; even more so keeping in mind that tax information is very sensitive and delicate, so it is not a secure process.
All of this work serves to deliver to another person the report of invoices issued, invoiced value, withholdings made and total value received so that they can perform other calculations and analyzes to report to the Internal Revenue Service.
Analyze the solution
I know what you need, now how can I help you?
Excel
A solution that is always in sight is to use the Excel functions: FIND, EXTRACT, VLOOKUP or XLOOKUP, CONCATENATE and VSTACK to solve this without problems; But yes, it requires a very elaborate process.
Then I consider the other option: using Power Query,
Power Query in Excel
Let's start with the Invoice consultation
Here you would load the first file that has the invoices on separate sheets (January, February, March), then perform a data cleansing ETL process on each query, removing the top rows of each file and promoting the first row as headers; Then I would create a new query to append the 3 sheets into a single query, ready for the first step. The attached file will be called Total Invoices.
Now let's go to the Diary query.
Having several records of the same invoice makes it complicated... how do I solve it?
I can duplicate the Journal query and in this query filter the records that have the IR retention by invoice, with the name Journal – IR .
I do the same by duplicating the Journal query and filter the records that have VAT withheld by invoice, with the name Journal – VAT .
So I'm going to work with these 3 queries:
Total invoices
Journal – IR
Journal – VAT
Final step
I consolidate the Total Invoices file with Journal – IR into a new query, using Combine Queries, which generates a new query that we will call Total Invoices – IR.
With Total Invoices – IR , I again use Combine Queries with the Journal – VAT query, which generates a new query that we will call Total Invoices – IR – VAT.
It should work.
I open Excel, load the files and follow all the processes I have planned.
With the files loaded, I made sure to change the Data Type of the invoice number to Text so I can combine the queries.
I continued with each stage that I have planned, making sure that each one of them is validated and correct until I finally get the query Total Invoices – IR – VAT.
I review the final query again and in the Power Query Editor I click Load and Close.
I call the client and show him the result.
He can't believe it. Is happy.
Summary
Knowing Excel and what I can do with functions was important.
But also knowing how Power Query works in Excel was decisive.
In Excel I had to program with functions, as I said before, nothing serious, but a little long.
In Power Query , a schedule was still performed, but I did not enter code, a process
clearly Low Code or No Code , only perform ETL processes, used Append Queries , duplicated queries, used Filters and finally used Combine Queries .
And it was a much faster process than doing it with functions alone.
If it would be you, analyzing what I just shared with you, this is the time to take our 2 Excel courses ( Basic Intermediate and/or Advanced Intermediate ) and the Power Query course, to become an expert in providing solutions for you, your internal clients or your external clients.
Take advantage of this exclusive discount!