Using the Power Query System for SAP ERP Data Science and Data Mining

Chernyshov M.Y, Tkachenko A.L.


Kaluga State University K.E. Tsiolkovsky, Kaluga, Russia
RU
EN

AUTHORS

  • Tkachenko
    Alexey Leonidovich

    Associate Professor of the Department of Informatics and Information Technologies
    Kaluga State University
    them. Tsiolkovsky


    PhD in Engineering sciences
  • Chernyshov
    Mikhail Yurievich

    Master’s Degree student
    Institute of Engineering and Technology
    Kaluga State University
    them. Tsiolkovsky



    Master’s degree courses - "IT systems in business and management"
Article Keywords: Power Query, SAP ERP data, data processing, data analysis, intelligent system.

This article provides an overview of the main features of Power Query and how they can be used to process data in conjunction with the SAP ERP system. It highlights the benefits of using Power Query, including faster data processing time, improved data accuracy, and the ability to easily share and reuse data processing procedures.

What are intelligent systems?

key features
  • Computer systems
    These are computer systems designed to perform tasks that would normally require human-level intelligence.
  • Self-learning systems
    These systems are capable of solving complex problems, making decisions, and even learning from experience.
  • Adaptive Systems
    One of the key characteristics of intelligent systems is their ability to adapt flexibly. They are able to continually improve their performance by analyzing data and adjusting their algorithms accordingly.
  • Ability to handle large amounts of data
    This makes them well suited for applications such as data analysis, where they can quickly identify patterns and make predictions based on that information.

Impact of intelligent systems on the financial system


Finance and intelligent systems and technologies have had a significant impact on the work of finance departments in recent years, as they are used to optimize investment strategies and minimize risks. The introduction of advanced technologies, such as Excel Power query systems, has significantly improved the efficiency and accuracy of financial transactions.

How does it work?

Excel Power Query

A data connection tool that allows users to access and manage data from various sources.

SAP ERP

Enterprise Resource Planning — is a software application developed by SAP to manage the core business processes of an organization.

Automation

The release of a person partially or completely from direct participation in the processes of obtaining, transforming, transmitting and using information.

Benefits of Sharing
Excel Power Query and SAP ERP

  • Increasing labor productivity
    Labor productivity refers to the basic characteristics of the performance of the company's personnel. Labor productivity is the amount of product produced per unit of work time. This numerical parameter helps to understand the qualitative effect of the labor expended.
  • Improving the quality of financial reports
    The insights provided by good reporting will benefit analysts as they evaluate performance and the future of the organization. Poor financial statements, on the other hand, contain incomplete or inaccurate information.
  • Reducing the number of errors
    The consequences of errors in financial reporting can be serious problems, due to which companies suffer losses, and loyalty to the financial system in general decreases. Errors always demonstrate problematic issues faced by financial analysts. Among other things, they illustrate the likely costs that are associated with irresponsible accounting practices that lead to unreliable indicators.
  • Optimization of management processes
    The main task of optimizing the management of the organization is to derive the final result of the management of the object. Its main goal is to increase the efficiency of activities while obtaining the greatest profit. And to achieve the goal, it is necessary to identify and eliminate or correct slowing factors.
  • System approach to problem solving
    Without optimizing the internal reporting system, it is impossible to optimize the work of the company as a whole. The problem of overloaded internal reporting can only be solved in a comprehensive manner, which is very laborious. It is most effective to analyze each internal document in order to study the frequency of its execution, identify duplication of information, receipt of information that is unnecessary for the recipient, etc.

Power Query provides several useful statistical tools.

How much can they help us in analyzing the data received from SAP ERP?

Directions for using Power Query:

  • Summary statistics
    the number, sum, mean, minimum, maximum, and standard deviation of the data;
  • Data aggregation
    combining data from multiple sources into a single dataset;
  • Data filtering
    deletion of unnecessary data according to certain criteria;
  • Pivot tables
    summarize and analyze data by converting it to another format;
  • Grouping data
    create data subsets based on one or more columns;
  • Conditional Formatting
    visual selection of data based on certain conditions;
  • Data type definition
    automatic detection and conversion of data types (for example, text to date).

RESEARCH METHODOLOGY

One example of the implementation of Power Query in the activities of the finance department working with SAP ERP is the combination of data from several sources (for example, bank statements) into a single financial report.

To demonstrate this example, let's create an analogue of statements from 3 different banks (tables 1-3) generated in the SAP ERP system after the initial download from client banks:
Table 1. Bank Statement No. 1

The following statements are downloaded from other banks, which affects the position of the columns of the uploaded tables, which complicates processing:

Table 2. Bank Statement No. 2

The next statement will also differ in the order of the columns from the previous ones:

Table 3. Bank Statement No. 3

One possible way to process bank statements downloaded from SAP ERP using Power Query

***
1) Open Microsoft Excel and select the "Data" tab.
2) Click on "Get Data" and select "From File" and then "From Workbook"
Figure 1. Retrieving data from a workbook

3) Select the first bank statement and click "Load" to import the data into Power Query

Figure 2. Retrieving data from a workbook

4) Go to the tab "View", "Advanced Editor". Let's transform the request into a function using the (filename)=> command in order to, by analogy, request data from other bank statements located in the same folder.

Figure 3. Converting a request to a function

5) Create a request to the folder "Bank statements". Download them and click "Change data".

Figure 4. Combining queries

6) Add a custom column, write the function written according to the first statement, and apply it to all files in the folder.

Figure 5. Applying a function to a shared folder

7) Expand the custom column. Use Power Query's data transformation features such as Filter Rows, Remove Columns, and Replace Values to clean up and transform data as needed. Finally, click "Close and Load" to load the processed data into an Excel sheet for further analysis and reporting.

Figure 6. Uploading summary information

  • From the considered example, it can be seen that 16 rows were loaded, no errors occurred.
  • Statements are combined on one sheet, while the number of statements after the written function-request can be freely increased.
  • Columns that have the same name, but were in a different order, were also brought to the same format.
  • Then you can apply filters on the required columns, or use the query for a new sheet function to sort the information.
  • In case of changing the data in the folder, you need to go to the "Data" tab and perform the "Update all" function.

This development allows you to get rid of manual processing of statements from SAP ERP, transferring them to Excel for further analytics, as well as make this process faster and eliminate human errors. In the example, a small amount of data was used, but when it comes to processing a large amount of Big Data data, it is immediately clear how relevant this topic is for an enterprise where it was done manually.

FINAL PROVISIONS

We've covered a basic example and the exact steps may vary depending on your specific requirements and the complexity of your data. In addition, in some cases you may need advanced features such as combining data from multiple sources and creating custom calculations.

Additional features that can be obtained through the integration of Power Query with SAP ERP:

Power Query provides several useful statistical tools that can help you analyze the data you get
from SAP ERP.
  • Updating and automating data
    Power Query can automate the process of updating data from SAP ERP, ensuring that data is always fresh and accurate.
  • Data Sharing and Collaboration
    Power Query makes it easy for organizations to share and collaborate with data from SAP ERP, improving data sharing and collaboration across the organization.
  • Combining and consolidating data
    Power Query can be used to combine and consolidate data from multiple sources, including SAP ERP, into a single, centralized dataset for analysis.
  • Custom Calculations
    Power Query allows users to create custom calculations and formulas to get meaningful insights from SAP ERP data.
  • Finding and fixing bugs
    Advanced error detection and correction features in Power Query help organizations minimize the risk of errors and improve the accuracy of data from SAP ERP.
  • Advanced Analytics
    Power Query provides a number of advanced analytics capabilities such as data modeling, data visualization, and predictive analytics that you can use to gain deeper insight into data from SAP ERP.
  • Performance optimization
    Power Query provides tools to optimize the performance of data science and analysis operations, enabling organizations to process large amounts of data from SAP ERP with ease.




In conclusion, I would like to note:
  • There are many benefits to using Power Query to process and analyze bank statements through SAP ERP.
  • Power Query's powerful data processing and transformation capabilities enable organizations to quickly and accurately analyze their financial data, making it easier to spot trends, improve decision making, and drive business growth.
  • In addition, the integration of Power Query with SAP ERP provides organizations with a centralized view of financial data in real time, allowing them to make informed decisions and optimize their operations.
  • Using Power Query to analyze bank statements can also help organizations reduce manual data processing and minimize the risk of errors, improving the accuracy and reliability of financial information.
  • Power Query offers a powerful solution for organizations looking to streamline their financial analysis processes and drive business success.