minbe-data 님의 블로그

This Power Query example demonstrates how to generate a monthly branch sales report using Table.Group. The query references an existing sales dataset, groups records by YearMonth and Branch, calculates the total net sales amount and transaction count, and finally sorts the results by month and sales performance. Key Power Query techniques demonstrated in this project include: • Referencing exist..

This Power Query example demonstrates how to create a monthly sales summary report using Table.Group. The query references an existing Sales dataset, groups records by YearMonth, calculates total sales, total net sales, and transaction counts, and finally sorts the results in chronological order. Key Power Query techniques demonstrated in this project include: • Referencing existing queries • Ta..

Creating a professional PowerPoint presentation no longer requires designing every slide manually. In this project, ChatGPT was used to create detailed presentation prompts, and Microsoft Copilot generated the PowerPoint slides automatically. The workflow included: - Designing presentation prompts with ChatGPT - Generating slides using Microsoft Copilot - Creating a professional Power BI portfol..

Managing sales data often requires combining multiple lookup tables before analysis. In this example, Power Query automatically merges three different mapping tables into a single sales dataset. The automation process includes: - Loading a sales table - Importing multiple mapping tables from an external Excel file - Matching Sales Manager by Branch and Item - Matching Category and Account by Ite..

Managing sales data from multiple Excel files can be challenging when each file has different column names and formats. In this example, Power Query automatically imports every Excel file from a folder and converts them into a standardized dataset. The automation process includes: - Importing multiple Excel files from a folder - Ignoring hidden and temporary files - Detecting tables or worksheet..

Managing stock lists manually can be time-consuming when different symbols require different cell formats. In this example, Python automatically analyzes stock symbols and applies predefined Excel formatting rules. The automation process includes: - Matching company names with stock symbols - Applying background colors automatically - Drawing diagonal lines - Adding cross patterns - Applying cus..

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