Automatically Assign Categories to Unassigned Rows in Power Query and DAX
A solution for automatically assigning office seats to organizational units.
1Scenario and Requirement
Office building seats need to be assigned to organizational units (OUs). Some seats have no assigned OU. It was decided to automatically assign these seats to the OU with the most seats in their room or floor. Power Query and DAX will be used to automate this process.
2Data Source and Structure
Data is stored in monthly updated Excel files with dates in filenames. Only the latest file should be used to assign unassigned seats. Performance and compatibility issues may arise during this process.
3Power Query Approach
An M-function was created in Power Query to perform transformations early. The function finds the latest file, filters rows for the current room, and assigns seats to the OU with most seats. However, this method is affected by network latency.
4Power Query Performance Issues
While working locally, this method suffered severe performance degradation on network folders (especially SharePoint). Reading files for each row caused significant data traffic and long loading times (1-2 hours).
5Power BI Service Incompatibility
Dynamic data sources not working in Power BI Service made the Power Query solution unusable. Changing file paths between executions prevented alternative solutions from being implemented.
6Transition to DAX Solution
Due to Power Query limitations, the solution was moved to DAX. Two calculated columns were created: one to mark the latest dataset, another to assign unassigned seats to appropriate OUs. This approach works more stably and quickly.
7Identifying Latest File with DAX
Using DAX, date information in filenames is compared to identify the most recent file. This operation is performed across all files loaded into the data model, marking only the latest one.
8OU Assignment with DAX
A two-step logic is applied for unassigned seats: first checking OUs in the same room, then evaluating floor-wide OUs if none are assigned. TOPN and FILTER functions identify the OU with most seats.
9Solution Advantages
The DAX solution eliminates Power Query's performance and compatibility issues. Working within the data model, it creates no network traffic and runs smoothly in Power BI Service. It also provides much faster results.
10Implementation Recommendations
For similar scenarios, transformations should preferably be done within the data model (using DAX). When using dynamic data sources, Power Query limitations should be considered. Performance tests should be conducted in different environments.
11Conclusion and Insights
This case demonstrates advantages and disadvantages of different tools in data preparation. While Power Query offers powerful transformations, DAX can provide more efficient solutions within the data model. Choosing the right tool depends on the scenario and environment.
Original article: Towards Data Science — Power Query ve DAX ile Kategorisiz Satırlara Otomatik Atama