Other opportunities
Download Print

Using Power Query to aggregate and compare information filed via EDS 1/5/23

Artis Vizbelis
PwC senior consultant, tax reporting, accounting and strategy

Thanks to the considerable interest in PwC Academy’s introductory lecture in November, which offered an overview of the content of PwC’s Digital Academy (still available as a video recording), in January PwC’s Digital Academy Latvia launched a series of three webinars for managers, accountants, bookkeepers and everyone who works with data, reports and standard processes on a daily basis.

We are happy to share with our MindLink.lv readers the topics we discuss in our webinars.

In our article of 27 September 2022 we explored what Power Query is like and why we should use it, as well as giving a brief overview of what the tool offers. This article explores how we can benefit from using Power Query to compare data, because participants of PwC Digital Academy Latvia’s upcoming webinar on 23 February will have the opportunity to find out how they can set up templates in practice for comparing various types of data.

Comparing data

When using accounting data, we have to prepare various types of reports for filing with the State Revenue Service (SRS) via the Electronic Declaration System (EDS). To make sure the data in our accounting system is correct and matches the information we file via EDS, the data needs comparing.

To retrieve information from EDS, we can use the search option and select the documents filed by type and download them in a viewable (PDF) or machine-readable format (XML). If we need to view this information, we will of course use PDF. But if we need to aggregate or compare this information for multiple tax periods simultaneously, then Power Query helps us use the machine-readable format and set up templates that will do this automatically.

We can download reports from EDS and put reports of one type for various periods into a designated folder then use Power Query to combine information from all the files in that folder. In the same template we can set up a connection with other files, such as main ledger data that may be displayed in a different format, such as CSV or XLS.

If we set up such a template once, we can ensure that after downloading more reports from EDS and adding them in the particular folder, our template will update this data by adding the new information. At the same time, inserting an updated file from our accounting system will update the data from the other system placed in our template, so we will have created a template that checks data matches between the two systems.

Benefits from using a Power Query template

  • We do not have to view and process EDS reports one at a time.
  • The new data, once updated, is added automatically.
  • We can put data coming from several data sources with different layouts into our template.
  • We can quickly identify mismatches between systems.

Practical examples where this functionality is useful for accountants

  • Checking to see if the information reported on VAT returns and the amount of tax payable/deductible matches the information in your accounting system
  • Checking to see if accurate information has been filed with the SRS, including unpaid leave and other types of leave as per the Cabinet of Ministers’ Rule No. 827 on mandatory national social insurance contributions
  • Comparing wage entries and related tax entries with data filed with the SRS
  • Checking to see if your accounting system shows any late fees the SRS has charged for taxes in the tax period
  • Comparing corporate income tax data with your accounting system
  • Comparing any other tax charges that are reported to the SRS

So, with Power Query we can set up templates that are tailored not only to EDS formats but also to the data format available in a particular accounting system or any other system. With the functionality available from Power Query we can easily transform data and hide any information we don’t need and lay it out in a way that makes it easy to compare it with data from a particular system and shows only the information we need.

To find out more about the features offered by Power Query and to study practical examples of how to aggregate and transform data from EDS, as well as other examples of data processing, we invite you to sign up and take part in PwC Digital Academy Latvia’s webinar on 23 February!

Share the article

If you have any comments on this article please email them to lv_mindlink@pwc.com

Ask question