Automatically Assign A Category To Uncategorized Rows In Power Query And DAX

Contents

The Scenario
The data source
Do it in Power Query
This is not practical
Do it in DAX
Conclusion
References

The Scenario

to report on seat ownership.

Each seat in the office building must be assigned to an organisational unit (OU).

There is a list of all seats, and the owning OU should be set for each seat. But some seats don’t have an owning OU set.

Therefore, they decided to assign all unassigned seats to the OU that owns the most seats.

This must be done by room and by floor.

Look at the following floor plan:

Figure 1 – Example floor plan with unassigned seats (This is not a real one. Just an example created by the Author)

Look at the marked seats in room B.

As you can notice, “OU 3” owns the most seats in that room.

Therefore, these seats must be assigned to that OU.

But when looking at the entire floor, “OU 5” own the most seats across all rooms.

If there is a room on that floor where all seats aren’t assigned, they must be assigned to “OU 5”.

These are the business rules for assigning unassigned seats.

The data source

All the data is stored in Excel files.

Each file has a date at the start of the filename.

For example:

20260331_Seatlist.xlsx
20260430_Seatlift.xlsx
20260531_Seatlist.xlsx

There is one file per month that contains all existing seats in the building, along with their assignments for that month.

This is because the assignments change over time and the user must be able to see the differences.

But only the latest dataset (Excel file) should be used to assign the unassigned seats.

Do it in Power Query

As you might know from my previous articles, I aim to perform data transformations as early as possible in the load chain.

Therefore, it was natural to start doing the work in Power Query.

What I needed to do was the following steps for each row without an assigned OU:

Find the latest file among all files loaded
Read this file
Search for the rows for the current room (The room in the current row)
Count the number of seats per OU in that room
Sort descending by the number of seats
Keep only the first row – the one with the OU that has the highest number of seats
Assign that OU to the current row

For this, I created an M-function [CheckMax_ForSeat].

The code for this function is comprised of the following segments:

1. Find the latest file:

Source = Folder.Files(SourceFolder & "Seatlists"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "20")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1)

This code works, as the date is at the beginning of the file name, as mentioned above.

2. Read the file:

#"Added Custom" = Table.AddColumn(#"Kept First Rows", "FullFilePath", each [Folder Path] & [Name], type text),
#"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "ReadSingleFile_Seatlist", each ReadSingleFile_Seatlist_ForSeat([FullFilePath])),
#"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"Name", "Date modified", "ReadSingleFile_Seatlist"}),
#"Expanded ReadSingleFile_Seatlist" = Table.ExpandTableColumn(#"Removed Other Columns", "ReadSingleFile_Seatlist", {"OU-No.", "Room-No."}, {"OU-No.", "Room-No."}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded ReadSingleFile_Seatlist",{{"OU-No.", type text}, {"Room-No.", type text}})

As you can see, I use a second M-Function to read the file: ReadSingleFile_Seatlist_ForSeat

This file is used to read the current file.

It contains the M-code to read an Excel file and keep only the needed columns.

You get the same M-code when you import a single Excel file in Power Query.

For this reason, I will not show it here.

3. Keep only the rows for the current room and get the OU with the highest number of seats:

#"Filter out Empty OE" = Table.SelectRows(#"Changed Type", each ([#"Room-No."] = RoomNo) and ([#"OU-No."] <> null)),
#"Grouped Rows" = Table.Group(#"Filter out Empty OU", {"OU-No.", "Assigned_OU"}, {{"Seat_Count", each Table.RowCount(_), Int64.Type}}),
#"Sorted Seat_Count" = Table.Sort(#"Grouped Rows",{{"Seat_Count", Order.Descending}, {"Assigned_OU", Order.Ascending}}),
#"Kept highest Seat Count" = Table.FirstN(#"Sorted Seat_Count",1)

I must perform these operations twice:

Once for the seats in the same room
Once for the rooms on the same floor

The result is two columns, containing the OU to assign according to the same room or the floor.

If the room has no assigned seats, set the OU to the OU with the highest number of seats on the entire floor.

Otherwise, take the OU with the most assigned seats in the same room.

It worked very well on my laptop.

But then…

This is not practical

I encountered two major issues:

As soon as I switched the source to a network-based folder, performance dropped drastically.

A SharePoint folder was the worst, followed by a shared folder on a file server.

The problem was that the M-functions mentioned above must be executed once for each row in the dataset.

This resulted in around 1 GB of data being read, while the total across the three available files is 300 KB.

The cause of the drop in performance wasn’t the amount of data read, as it worked well on my laptop. The reason was network traffic latency. Each round trip cost time, which resulted in a large amount of time needed to load the data,

By the way, the Power BI file is only 4 MB after loading the data and assigning the OUs to all seats.

It took around one hour to load the data from the shared folder – over two hours from SharePoint.

The other issue was even more severe.

While it worked in Power BI Desktop, it didn’t work after publishing it to the Service.

The reason is that dynamic data sources are not allowed in Power Query.

Here are some links about this issue.

I tried the approaches mentioned there, but they were not applicable because the path always changes between executions, since the latest file can change.

Therefore, I was forced to abandon this approach and rethink how to solve it.

Do it in DAX

Now it’s DAX’s turn.

I must create two calculated columns:

Mark the newest dataset/file
Assign the OU to unassigned seats

The DAX expression to mark the newest file is very simple:

IsNewestFile = 
    VAR LatestFileDate = 
            CALCULATE(MAX('Raumliste_HP'[FileDate])
                        ,REMOVEFILTERS('Roomlist')
                        )

RETURN
    IF( LatestFileDate = 'Raumliste_HP'[FileDate]
            ,TRUE()
            ,FALSE()
            )

The first step is to leverage context transition to get the date of the newest file.

To achieve this, I extracted the first 8 characters of the file name (e.g., 20260731_Seatlist.xlsx) and stored them in the [FileDate] column. I did this in Power Query.

Then, I compared the FileDate of the current row to that value.

When it matches, I assign TRUE; if not, FALSE.

Next, I added another calculated column to assign the OU to each seat.

To develop this logic, I wrote a DAX query to simulate and test the results for one room at a time.

First, I created a list of assigned OUs for one specific room from the newest dataset (The column names are in German, as I worked with a German dataset):

DEFINE
    VAR RoomNr = "HP 8D01"
    
    VAR ListOfOU = CALCULATETABLE(
                        SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                        ,Raumliste_HP[Rauminhaber_OE]
                                    )
                                    ,REMOVEFILTERS(Raumliste_HP)
                                    ,Raumliste_HP[Raum-Nr.] = RoomNr
                                    ,Raumliste_HP[IsNewestFile] = TRUE()
                                    )
                                    
EVALUATE
    ListOfOU

This is the result:

Figure 2 – The query and the result of all assigned OUs for one room (Figure by the Author)

Next, I exclude the rows without an [Assigned_OU] (Column Rauminhaber_OE), and I count the rows for the remaining rows:

DEFINE
    VAR RoomNr = "HP 8D01"
    
    VAR ListOfOU = CALCULATETABLE(
                        SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                        ,Raumliste_HP[Rauminhaber_OE]
                                        ,"@RowNo", COUNTROWS(Raumliste_HP)
                                    )
                                    ,REMOVEFILTERS(Raumliste_HP)
                                    ,Raumliste_HP[Raum-Nr.] = RoomNr
                                    ,Raumliste_HP[IsNewestFile] = TRUE()
                                    ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                    )
                                    
EVALUATE
    ListOfOU

Here you see the result:

Figure 3 – This is the result of the query to count the rows (seat) for each OU (Figure by the Author)

The third step is to order the result by the number of rows in descending order:

DEFINE
    VAR RoomNr = "HP 8D01"
    
    VAR ListOfOU = CALCULATETABLE(
                        SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                        ,Raumliste_HP[Rauminhaber_OE]
                                        ,"@RowNo", COUNTROWS(Raumliste_HP)
                                    )
                                    ,REMOVEFILTERS(Raumliste_HP)
                                    ,Raumliste_HP[Raum-Nr.] = RoomNr
                                    ,Raumliste_HP[IsNewestFile] = TRUE()
                                    ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                    )

EVALUATE
    ListOfOU
    ORDER BY [@RowNo] DESC

The result of this query is this:

Figure 4 – Result after sorting the result by the number of rows (Figure by the Author)

The last step is to get the first row, a.k.a. the OU with the most assigned seats:

DEFINE
    VAR RoomNr = "HP 8D01"
    
    VAR ListOfOU = CALCULATETABLE(
                    SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                    ,Raumliste_HP[Rauminhaber_OE]
                                    ,"@RowNo", COUNTROWS(Raumliste_HP)
                                )
                                ,REMOVEFILTERS(Raumliste_HP)
                                ,Raumliste_HP[Raum-Nr.] = RoomNr
                                ,Raumliste_HP[IsNewestFile] = TRUE()
                                ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                )


EVALUATE
    TOPN( 1
        ,ListOfOU
            ,[@RowNo], DESC
            )

The result is one row:

Figure 5 – The query and the result to get the first row (Figure by the Author)

As you can see, I can omit the ORDER BY as the parameters in line 82 have the same effect.

But what happens if multiple OUs have the same number of assigned seats in one room?

The query above will return two rows, as TOPN() cannot distinguish between them:

Figure 6 – Query resulting in two rows with the same Row-Count (Figure by the Author)

We can have the most elaborate algorithm to answer this question, or we can add the OU as a sorting column to get one row:

DEFINE
    VAR RoomNr = "HP 8B01"
    
    VAR ListOfOU = CALCULATETABLE(
                    SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                    ,Raumliste_HP[Rauminhaber_OE]
                                    ,"@RowNo", COUNTROWS(Raumliste_HP)
                                )
                                ,REMOVEFILTERS(Raumliste_HP)
                                ,Raumliste_HP[Raum-Nr.] = RoomNr
                                ,Raumliste_HP[IsNewestFile] = TRUE()
                                ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                )


EVALUATE
    TOPN( 1
        ,ListOfOU
            ,[@RowNo], DESC
            ,[Rauminhaber_OE], ASC
            )

Now, we get only one row in the result:

Figure 6 – Handling ties by adding the column with the assigned_OU as a sorting column (Figure by the Author)

Now I have the full logic to assign an OU to each seat in a room.

This is the code of the calculated column:

Assigned OU_ByRoom =
    VAR RoomNo = 'Raumliste_HP'[Raum-Nr.]

    VAR ListOfOU = CALCULATETABLE(
                    SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                    ,Raumliste_HP[Rauminhaber_OE]
                                    ,"@RowNo", COUNTROWS(Raumliste_HP)
                                )
                                ,REMOVEFILTERS(Raumliste_HP)
                                ,Raumliste_HP[Raum-Nr.] = RoomNo
                                ,Raumliste_HP[IsNewestFile] = TRUE()
                                ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                )
    VAR Result =
                TOPN( 1
                    ,ListOfOU
                        ,[@RowNo], DESC
                        ,[Rauminhaber_OE], ASC
                        )
RETURN
IF(NOT ISBLANK('Raumliste_HP'[Rauminhaber_OE])
    ,'Raumliste_HP'[Rauminhaber_OE]
    ,SUMMARIZE(Result
                ,[Rauminhaber_OE])
    )

Now I must apply the same logic to the entire floor.

I add a second calculated column using almost the same logic, but this time for the floor rather than the room.

Finally, I must assign the calculated OU assignment to the rows without an OU assignment.

This will follow the same logic as I implemented in the Power Query solution.

I did it with a SWITCH() statement:

Assigned seat OU =
    SWITCH(TRUE()
        ,ISBLANK('Raumliste_HP'[Rauminhaber_OE]) && ISBLANK('Raumliste_HP'[Assigned OU_ByRoom])
            ,'Raumliste_HP'[Assigned OU_ByFloor]
        ,ISBLANK('Raumliste_HP'[Rauminhaber_OE])
            ,'Raumliste_HP'[Assigned OU_ByRoom]
        ,'Raumliste_HP'[Rauminhaber_OE])

The final result looks like this:

Figure 7 – A few rows with the final selection. When the assignment by room wasn’t possible, the OU is assigned by floor (Figure by the Author)

As you can see, the OU is assigned by room when a value is present. If no assignment is possible by room, the OU is assigned by floor.

Conclusion

It was an interesting journey to build this solution.

I followed the principle of transforming data as early as possible, and the result wasn’t viable.

Even though it worked in Power BI Desktop with local files.

In my opinion, this is a flaw in Power BI: I can build a solution in Power BI and not receive any warning or indication that it might not work during development or while publishing it to the cloud service.

This is the second time I experienced this.

The other situation was when I combined cloud data with on-premises data. It worked in PBI Desktop, but the data couldn’t be loaded into the cloud service. The error message was that it was not allowed to load data from different sources, even though I set the Privacy level to the same (the cloud source was a company SharePoint Online folder).

In both situations, I was forced to build the solution in the data model and with DAX.

In the case described here, the DAX solution was less complex than the Power Query solution. But this is not always the case.

Knowing the limitations of Power Query in the cloud helps avoid spending too much time building a solution that must be refactored due to unsupported combinations of data or performance issues.

But this knowledge comes over time.

Anyway, there is an issue when doing it in DAX compared to doing it in Power Query: now, I have columns in the data model that contain intermediary results.

I solved it by adding the “_original” appendix to the columns to be replaced from the original data. In addition, I set these columns as hidden and added them to a Display folder named “Intermediary columns/Do-not-use”. This way, I can ensure that they aren’t used.

This can be avoided by preparing the data earlier. Then I can remove the intermediary columns and load only the columns with cleansed data into the data model.

As a conclusion, my realisation was that the “right way” of doing something is not always the correct way.

Don’t dogmatically hold on to the “right way”. Think critically and select the correct way to do something based on your experience.

But it’s the normal process of learning to make a wrong decision. This happens to everybody.

Don’t be afraid of it.

References

The data are fictitious and generated by me.

There is no relationship to real data.

Here you can learn more about context transition in DAX:

Automatically Assign a Category to Uncategorized Rows in Power Query and DAX