minbe-data 님의 블로그

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..