minbe-data 님의 블로그

Managing meal plans manually can be time-consuming, especially when ingredient quantities must be adjusted based on the number of people. In this example, Power Query and Python work together to automate the entire process. Power Query automatically: - Merges meal plans with recipe data - Calculates ingredient quantities - Adjusts quantities by person count - Creates a clean meal planning datase..

In Excel expense reports, card usage data is often stored in a separate table. When preparing an expense report, we usually need to bring only the transactions that match a selected date and management number. In this example, XLOOKUP is used to bring key report information, while Power Query filters the related card usage records automatically. Power Query checks: - Report Date - Management Num..

Tracking employee workdays can be difficult when working hours are recorded across multiple day columns. In many attendance sheets, employees are listed in rows while daily work hours are stored horizontally. In this example, Power Query automatically: - Converts daily work hours into a structured table - Identifies weekdays and holidays - Applies custom workday rules - Counts qualifying weekday..

Many pricing tables use both grade levels and amount ranges to determine fees. For example, the fee may vary depending on: - Customer Grade - Contract Amount - Loan Amount - Sales Amount - Property Value In this example, Power Query automatically finds the correct fee by comparing the selected Grade and Base Amount against a predefined fee table. The result updates automatically whenever the inp..

Many Excel reports store product sales data in separate sections across a worksheet. For example, each product may have its own Buyer, Quantity, and Amount columns. While this layout is easy to read, it is difficult to analyze and summarize. In this example, Power Query automatically: - Extracts multiple product sections - Standardizes column names - Combines all product tables - Removes blank r..

Managing inventory data in Excel often becomes difficult when daily usage and receipt records accumulate over time. In this example, Power Query automatically creates a monthly inventory summary report. The process includes: - Replacing null values with zero - Extracting month numbers from dates - Grouping data by month and year - Calculating total receipts - Summing product usage - Retrieving t..

In Excel, category names and category codes are often stored in separate tables. The problem is that category names may contain extra spaces, making exact matching difficult. In this example, Power Query automatically: - Merges two tables using CategoryName - Retrieves matching CategoryNo values - Removes unnecessary columns - Cleans text using Trim - Filters unmatched rows - Sorts the final res..

Many Excel reports contain monthly sections such as Jan, Feb, and Mar mixed together in a single worksheet. This type of structure is difficult to analyze because the data is not arranged in a proper table format. In this example, Power Query automatically: - Detects month header rows - Fills month values downward - Cleans supplier data - Unpivots the report structure - Combines month and attrib..

In Excel, monthly purchase data is often stored in a messy report format. For example, month names such as Jan, Feb, and Mar may appear as section headers, while supplier and purchase information are listed underneath. This structure is difficult to analyze directly with Pivot Tables. In this example, Power Query automatically cleans the report by: - Detecting month header rows - Filling month v..

In many Excel scenarios, you may need to rank only specific rows based on a condition. This becomes more complex when duplicate scores exist and additional tie-break rules are required. In this tutorial, you will learn how to rank only selected records using Power Query. Tie-breaking is handled automatically using a secondary column such as count. The ranking updates dynamically whenever the dat..