El escenario
para informar sobre la propiedad de los asientos.
Cada asiento en el edificio de oficinas debe asignarse a una unidad organizativa (OU).
Hay una lista de todos los puestos y se debe establecer la unidad organizativa propietaria de cada puesto. Pero algunos asientos no tienen un conjunto de unidades organizativas propietarias.
Por lo tanto, decidieron asignar todos los escaños no asignados a la unidad organizativa que posee la mayor cantidad de escaños.
Esto debe hacerse por habitación y por piso.
Mire el siguiente plano de planta:
Mire los asientos marcados en la sala B.
Como puedes notar, “OU 3” posee la mayor cantidad de asientos en esa sala.
Por lo tanto, estos asientos deben asignarse a esa OU.
Pero cuando se mira todo el piso, “OU 5” posee la mayor cantidad de asientos en todas las salas.
Si hay una sala en ese piso donde no están asignados todos los asientos, se deben asignar a “OU 5”.
Estas son las reglas comerciales para asignar asientos no asignados.
la fuente de datos
Todos los datos se almacenan en archivos de Excel.
Cada archivo tiene una fecha al comienzo del nombre del archivo.
Por ejemplo:
20260331_Seatlist.xlsx 20260430_Seatlift.xlsx 20260531_Seatlist.xlsx
Hay un archivo por mes que contiene todos los asientos existentes en el edificio, junto con sus asignaciones para ese mes.
Esto se debe a que las asignaciones cambian con el tiempo y el usuario debe poder ver las diferencias.
Pero sólo se debe utilizar el conjunto de datos más reciente (archivo Excel) para asignar los asientos no asignados.
Hazlo en Power Query
Como sabrá por mis artículos anteriores, mi objetivo es realizar transformaciones de datos lo antes posible en la cadena de carga.
Por lo tanto, era natural empezar a trabajar en Power Query.
Lo que tenía que hacer eran los siguientes pasos para cada fila sin una unidad organizativa asignada:
Encuentre el último archivo entre todos los archivos cargados Lea este archivo Busque las filas de la sala actual (La sala en la fila actual) Cuente el número de asientos por OU en esa sala Ordene de forma descendente por el número de asientos Mantenga solo la primera fila, la que tiene la OU que tiene el mayor número de asientos Asigne esa OU a la fila actual
Para ello, creé una función M [CheckMax_ForSeat].
El código para esta función se compone de los siguientes segmentos:
1. Busque el archivo más reciente:
Fuente = Folder.Files(SourceFolder & "Seatlists"), #"Filas filtradas" = Table.SelectRows(Fuente, cada Text.StartsWith([Name], "20")), #"Filas ordenadas" = Table.Sort(#"Filas filtradas",{{"Name", Order.Descending}}), #"Kept First Rows" = Table.FirstN(#"Filas ordenadas",1)
Este código funciona, ya que la fecha está al principio del nombre del archivo, como se mencionó anteriormente.
2. Lea el archivo:
#"Agregado personalizado" = Table.AddColumn(#"Mantuvo las primeras filas", "FullFilePath", cada [Ruta de carpeta] y [Nombre], escriba texto), #"Función personalizada invocada" = Table.AddColumn(#"Agregado personalizado", "ReadSingleFile_Seatlist", cada ReadSingleFile_Seatlist_ForSeat([FullFilePath])), #"Otras columnas eliminadas" = Table.SelectColumns(#"Función personalizada invocada",{"Nombre", "Fecha de modificación", "ReadSingleFile_Seatlist"}), #"ReadSingleFile_Seatlist ampliada" = Table.ExpandTableColumn(#"Otras columnas eliminadas", "ReadSingleFile_Seatlist", {"OU-No.", "Room-No."}, {"OU-No.", "Room-No."}), #"Cambiado Type" = Table.TransformColumnTypes(#"ReadSingleFile_Seatlist expandido",{{"OU-No.", escriba texto}, {"Room-No.", escriba texto}})
Como puede ver, utilizo una segunda función M para leer el archivo: ReadSingleFile_Seatlist_ForSeat
Este archivo se utiliza para leer el archivo actual.
Contiene el código M para leer un archivo de Excel y conservar sólo las columnas necesarias.
Obtiene el mismo código M cuando importa un único archivo de Excel en Power Query.
Por este motivo no lo mostraré aquí.
3. Mantenga solo las filas de la sala actual y obtenga la unidad organizativa con el mayor número de asientos:
#"Filtrar OE vacío" = Table.SelectRows(#"Tipo cambiado", cada ([#"Room-No."] = RoomNo) y ([#"OU-No."] <> null)), #"Filas agrupadas" = Table.Group(#"Filtrar OU vacía", {"OU-No.", "Assigned_OU"}, {{"Seat_Count", cada Table.RowCount(_), Int64.Type}}), #"Sorted Seat_Count" = Table.Sort(#"Grouped Rows",{{"Seat_Count", Order.Descending}, {"Assigned_OU", Order.Ascending}}), #"Mantuvo el mayor número de asientos" = Table.FirstN(#"Sorted Seat_Count",1)
Debo realizar estas operaciones dos veces:
Una vez para los asientos de la misma habitación Una vez para las habitaciones del mismo piso
El resultado son dos columnas que contienen la OU a asignar según la misma habitación o piso.
Si la sala no tiene asientos asignados, configure la unidad organizativa como la unidad organizativa con el mayor número de asientos en todo el piso.
De lo contrario, tome la unidad organizativa con más asientos asignados en la misma sala.
Funcionó muy bien en mi computadora portátil.
Pero entonces…
Esto no es practico
Encontré dos problemas importantes:
Tan pronto como cambié la fuente a una carpeta basada en red, el rendimiento disminuyó drásticamente.
Una carpeta de SharePoint fue la peor, seguida de una carpeta compartida en un servidor de archivos.
El problema era que las funciones M mencionadas anteriormente deben ejecutarse una vez para cada fila del conjunto de datos.
Esto resultó en la lectura de alrededor de 1 GB de datos, mientras que el total de los tres archivos disponibles es de 300 KB.
La causa de la caída en el rendimiento no fue la cantidad de datos leídos, ya que funcionó bien en mi computadora portátil. La razón fue la latencia del tráfico de la red. Cada viaje de ida y vuelta costó tiempo, lo que resultó en una gran cantidad de tiempo necesario para cargar los datos.
Por cierto, el archivo Power BI tiene solo 4 MB después de cargar los datos y asignar las unidades organizativas a todos los asientos.
Se tardó alrededor de una hora en cargar los datos de la carpeta compartida (más de dos horas desde SharePoint).
La otra cuestión era aún más grave.
Si bien funcionó en Power BI Desktop, no funcionó después de publicarlo en el Servicio.
El motivo es que las fuentes de datos dinámicas no están permitidas en Power Query.
Aquí hay algunos enlaces sobre este tema.
Probé los enfoques mencionados allí, pero no fueron aplicables porque la ruta siempre cambia entre ejecuciones, ya que el último archivo puede cambiar.
Por lo tanto, me vi obligado a abandonar este enfoque y repensar cómo solucionarlo.
Hazlo en DAX
Ahora es el turno del DAX.
Debo crear dos columnas calculadas:
Marcar el conjunto de datos/archivo más reciente. Asignar la unidad organizativa a puestos no asignados.
La expresión DAX para marcar el archivo más nuevo es muy simple:
IsNewestFile = VAR LatestFileDate = CALCULATE(MAX('Raumliste_HP'[FileDate]) ,REMOVEFILTERS('Roomlist') ) RETURN IF( LatestFileDate = 'Raumliste_HP'[FileDate] ,TRUE() ,FALSE() )
El primer paso es aprovechar la transición de contexto para obtener la fecha del archivo más reciente.
Para lograr esto, extraje los primeros 8 caracteres del nombre del archivo (por ejemplo, 20260731_Seatlist.xlsx) y los almacené en la columna [FileDate]. Hice esto en Power Query.
Luego, comparé FileDate de la fila actual con ese valor.
Cuando coincide, asigno VERDADERO; si no, FALSO.
A continuación, agregué otra columna calculada para asignar la unidad organizativa a cada asiento.
Para desarrollar esta lógica, escribí una consulta DAX para simular y probar los resultados de una habitación a la vez.
Primero, creé una lista de unidades organizativas asignadas para una sala específica a partir del conjunto de datos más nuevo (los nombres de las columnas están en alemán, ya que trabajé con un conjunto de datos alemán):
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() ) EVALUAR ListaDeOU
Este es el resultado:
A continuación, excluyo las filas sin [Assigned_OU] (Columna Rauminhaber_OE) y cuento las filas para las filas restantes:
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() ,NO ES BLANCO(Raumliste_HP[Rauminhaber_OE]) ) EVALUAR ListOfOU
Aquí ves el resultado:
El tercer paso es ordenar el resultado por el número de filas en orden descendente:
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() ,NO ES BLANCO(Raumliste_HP[Rauminhaber_OE]) ) EVALUAR ListOfOU ORDEN POR [@RowNo] DESC
El resultado de esta consulta es este:
El último paso es conseguir la primera fila, también conocida como la unidad organizativa con más asientos asignados:
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() ,NO ES BLANCO(Raumliste_HP[Rauminhaber_OE]) ) EVALUAR TOPN( 1 ,ListOfOU ,[@RowNo], DESC )
El resultado es una fila:
Como puede ver, puedo omitir ORDER BY ya que los parámetros en la línea 82 tienen el mismo efecto.
Pero, ¿qué sucede si varias unidades organizativas tienen el mismo número de asientos asignados en una sala?
La consulta anterior devolverá dos filas, ya que TOPN() no puede distinguir entre ellas:
Podemos tener el algoritmo más elaborado para responder esta pregunta, o podemos agregar la unidad organizativa como una columna de clasificación para obtener una fila:
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() ,NO ES BLANCO(Raumliste_HP[Rauminhaber_OE]) ) EVALUAR TOPN( 1 ,ListOfOU ,[@RowNo], DESC ,[Rauminhaber_OE], ASC )
Ahora, obtenemos sólo una fila en el resultado:
Ahora tengo la lógica completa para asignar una unidad organizativa a cada asiento en una sala.
Este es el código de la columna calculada:
Asignado OU_ByRoom = VAR RoomNo = 'Raumliste_HP'[Raumliste_Nr.] VAR ListOfOU = CALCULATETABLE( SUMMARIZECOLUMNS(Raumliste_HP[Raumliste_Nr.] ,Raumliste_HP[Rauminhaber_OE] ,"@RowNo", COUNTROWS(Raumliste_HP) ) ,REMOVEFILTERS(Raumliste_HP) ,Raumliste_HP[Raum-Nr.] = RoomNo ,Raumliste_HP[IsNewestFile] = TRUE() ,NO ES EN BLANCO(Raumliste_HP[Rauminhaber_OE]) ) VAR Resultado = TOPN( 1 ,ListOfOU ,[@RowNo], DESC 1
Ahora debo aplicar la misma lógica a todo el piso.
Agrego una segunda columna calculada usando casi la misma lógica, pero esta vez para el piso en lugar de la habitación.
Finalmente, debo asignar la asignación de OU calculada a las filas sin asignación de OU.
Esto seguirá la misma lógica que implementé en la solución Power Query.
Lo hice con una declaración SWITCH():
Asiento asignado OU = SWITCH(TRUE() ,ISBLANK('Raumliste_HP'[Rauminhaber_OE]) && ISBLANK('Raumliste_HP'[OU_ByRoom asignada]) ,'Raumliste_HP'[OU_ByFloor asignada] ,ISBLANK('Raumliste_HP'[Rauminhaber_OE]) ,'Raumliste_HP'[OU_ByRoom asignada] ,'Raumliste_HP'[Rauminhaber_OE])
El resultado final se ve así:
Como puede ver, la unidad organizativa se asigna por habitación cuando hay un valor presente. Si no es posible realizar una asignación por habitación, la OU se asigna por piso.
Conclusión
Fue un viaje interesante para construir esta solución.
Seguí el principio de transformar datos lo antes posible y el resultado no fue viable.
Aunque funcionó en Power BI Desktop con archivos locales.
En mi opinión, esto es un defecto de Power BI: puedo crear una solución en Power BI y no recibir ninguna advertencia o indicación de que podría no funcionar durante el desarrollo o mientras la publico en el servicio en la nube.
Esta es la segunda vez que experimento esto.
La otra situación fue cuando combiné datos en la nube con datos locales. Funcionó en PBI Desktop, pero los datos no se pudieron cargar en el servicio en la nube. El mensaje de error fue que no se permitía cargar datos de diferentes fuentes, aunque configuré el mismo nivel de privacidad (la fuente de la nube era una carpeta de SharePoint Online de la empresa).
En ambas situaciones, me vi obligado a crear la solución en el modelo de datos y con DAX.
En el caso que se describe aquí, la solución DAX era menos compleja que la solución Power Query. Pero este no es siempre el caso.
Conocer las limitaciones de Power Query en la nube ayuda a evitar perder demasiado tiempo creando una solución que debe refactorizarse debido a combinaciones de datos no compatibles o problemas de rendimiento.
Pero este conocimiento llega con el tiempo.
De todos modos, hay un problema al hacerlo en DAX en comparación con hacerlo en Power Query: ahora tengo columnas en el modelo de datos que contienen resultados intermedios.
Lo resolví agregando el apéndice "_original" a las columnas que se reemplazarán a partir de los datos originales. Además, configuré estas columnas como ocultas y las agregué a una carpeta de visualización llamada "Columnas intermedias/No usar". De esta manera, puedo asegurarme de que no se utilicen.
Esto se puede evitar preparando los datos con antelación. Luego puedo eliminar las columnas intermedias y cargar solo las columnas con datos limpios en el modelo de datos.
Como conclusión, me di cuenta de que la “forma correcta” de hacer algo no siempre es la forma correcta.
No te aferres dogmáticamente al “camino correcto”. Piensa críticamente y selecciona la forma correcta de hacer algo según tu experiencia.
Pero es el proceso normal de aprender a tomar una decisión equivocada. Esto le pasa a todo el mundo.
No le tengas miedo.
Referencias
Los datos son ficticios y generados por mí.
No hay relación con datos reales.
Aquí puede obtener más información sobre la transición de contexto en DAX: