Consolidar datos de varios libros con Power Query | Curso de Power Query en Excel

Power Query: Consolidating Multiple Files

Introduction to Power Query

• Víctor introduces the session, focusing on Chapter 5 of the Power Query course, highlighting previous lessons on tools like column splitting and data import from the web.

• The lesson aims to address a common task: consolidating multiple files within Power Query for easier data analysis.

The Need for Data Consolidation

• Users often create separate Excel files for each year (e.g., 2016, 2017), leading to challenges when generating reports that require comparisons across years.

• Without consolidation, users must manually combine data from different files, which is time-consuming and inefficient.

Using Power Query for File Consolidation

• Víctor explains that Power Query allows users to consolidate multiple files located in a single folder with minimal steps.

• He emphasizes the simplicity and utility of this tool by preparing an example involving three yearly sales data files (2015, 2016, and 2017).

Exploring Example Files

• Víctor opens the first file containing sales data for 2015, confirming it has over 6,000 records.

• He checks the subsequent files for 2016 and 2017, noting similar record counts in each.

Manual vs. Automated Data Handling

• If unaware of Power Query's capabilities, users would need to copy and paste records from each file into one document—a tedious process.

• This manual method becomes impractical when dealing with numerous files (20 or more), making automation through Power Query essential.

Steps to Use Power Query

• Víctor prepares to demonstrate how to use Power Query by opening a new Excel file and navigating to the "Data" tab where tools for obtaining and transforming data are located.

Power Query: Combining Excel Files

Introduction to Power Query and File Selection

• The speaker begins by accessing files from the desktop, specifically focusing on sales data for the years 2015, 2016, and 2017.

• They initiate the process of combining and editing these files in Power Query, indicating potential changes may be needed within the data.

Selecting Data Structure

• The speaker selects a table structure common across all three Excel files, which includes columns such as client name, account details, dates, item keys, descriptions, initial stock levels, material outputs, and costs.

• Additional columns include user information along with month and year indicators.

Data Processing in Power Query

• Once initiated, Power Query processes the data without displaying intermediate steps. The speaker checks for data integrity by filtering based on year.

• Initially only 2015 appears; however, they note that this is due to large data volumes being processed incrementally.

Verifying Combined Data

• After loading more data through filters, it confirms that records from all three years (2015–2017) are now available in a single query.

• The speaker emphasizes that even if not all filters show initially visible results due to volume limits in Power Query's display.

Understanding Data Columns

• A new column named "source name" is created to identify which file each record originates from—important for classification purposes.

• Various fields are reviewed for their data types (e.g., text or number), ensuring they are correctly formatted for analysis.

Finalizing Data Types and Loading Results

• Adjustments are made to ensure numerical values (like prices and quantities) are set as integers where necessary.

• The speaker explains how changes can be made without creating additional steps unless desired.

Conclusion of Process

• After processing approximately 21 thousand records efficiently through automation via Power Query rather than manual consolidation of multiple files.

Dynamic Tables in Excel: A Step-by-Step Guide

Introduction to Recommended Pivot Tables

• The speaker introduces the concept of inserting recommended pivot tables in Excel, highlighting that users may not always know what type of table they need.

• By selecting "recommended pivot tables," Excel suggests various reports based on the available data, simplifying the process for users.

Creating a Basic Pivot Table

• The speaker demonstrates how to create a pivot table from sales data, focusing on yearly sales figures.

• A dynamic chart is created to visualize sales over the years, showcasing significant sales figures for 2015 and 2016.

Enhancing Data Presentation

• The speaker discusses copying existing tables and modifying them to include monthly comparisons alongside yearly data.

• Emphasis is placed on addressing empty cells in pivot tables by displaying zeros instead of leaving them blank, which improves clarity for users unfamiliar with Excel.

Analyzing Sales Data by Client

• Another pivot table is created focusing on client sales rather than just year or month, allowing for a more detailed analysis of customer performance.

• The importance of sorting data in descending order based on total sales is highlighted to prioritize information effectively.

Finalizing Reports and Dashboards

• The speaker stresses the need for proper formatting and graphical representation when creating dashboards for management use.

• Automation of report generation through tools like Power Query is discussed as a means to streamline data consolidation across multiple years.

Key Takeaways on Data Analysis Efficiency

• A shift in focus from processing (80%) to analysis (20%) is encouraged, promoting efficient use of time and resources in generating insights from data.