Power Query Synthax

Me gustaría crear una columna personalizada (la naranja) en mi tabla, que me diga la evolución del estado de un proyecto en función de la versión de tiempo. Por ejemplo, aquí, en mi ID de proyecto, mis proyectos aparecen tantas veces como se haya guardado una versión de tiempo diferente. Pero me gustaría escribir una fórmula diciéndome en una nueva columna la evolución, si la hay, del estado de cada proyecto entre la versión LIVE y los demás. Sin embargo, no tengo ni idea de cómo podría hacer eso y qué es el Synthax …

¡Gracias por ayudar!

Los criterios exactos de verdadero o falso aún no están claros para mí, pero lo intenté.

Creé un archivo de Excel con los datos.

En Power BI, creé el código de Power Query a continuación. Incluye explicaciones (las líneas que comienzan con //).

Puede copiar el código: en Power BI, elija «Obtener datos» – Consulta en blanco – Editor avanzado y reemplace el código predeterminado con el código siguiente. Ajuste la fuente de datos a la suya, elija «Listo» y luego, en la pestaña Inicio, elija Cerrar y cargar.

También creé este video que lo lleva a través de los pasos de la consulta. No es una grabación en vivo de la creación de la consulta, sino un tutorial después de que creé la consulta.

Posiblemente aún se requiera algún ajuste detallado, pero esto debería estar cerca del resultado final.

let
    // Next 3 steps are created when importing the data from Excel
    Source = Excel.Workbook(File.Contents("C:UsersMarcelDocumentsForum bijdragenPower BI CommunityPower Query Syntax - Project status changes.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"PROJECT ID", Int64.Type}, {"STATUS", type text}, {"DATE_VERSION", type text}}),

    // Add Index for the original sort so the data can be sort back at the end
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Original Sort", 1, 1),

    // Add column with real date from [DATE VERSION]. "nl-NL" is used so format DD-MM-YYYY is recognized. 31-12-9999 for "LIVE", so it will sort at the end
    #"Added Custom" = Table.AddColumn(#"Added Index", "Version_Date", each if [DATE_VERSION] = "LIVE" then #date(9999,12,31) else Date.From(Text.Replace([DATE_VERSION],"_","-"),"nl-NL"), type date),

    // Sort on Project and date
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"PROJECT ID", Order.Ascending}, {"Version_Date", Order.Ascending}}),

    // Add 2 indices so the table can be merged with itself, such that the project and status from the previous row will be on the current row
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"},"Previous",JoinKind.LeftOuter),

    // After the merge, we need the previous project ID and the previous status
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"PROJECT ID", "STATUS"}, {"Previous.PROJECT ID", "Previous.STATUS"}),

    // Add a column with true if: the current version = "LIVE" and the Project ID = Previous Project ID and the Status <> Previous Status, else false 
    #"Added Custom1" = Table.AddColumn(#"Expanded Previous", "LIVE Status <> Previous Status?", each [DATE_VERSION] = "LIVE" and [PROJECT ID] = [Previous.PROJECT ID] and [STATUS] <> [Previous.STATUS], type logical),

    // Sort back to the original sort
    #"Sorted Rows1" = Table.Sort(#"Added Custom1",{{"Original Sort", Order.Ascending}}),

    // Remove columns that are no longer required
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Original Sort", "Version_Date", "Index", "Index.1", "Previous.PROJECT ID", "Previous.STATUS"})
in
    #"Removed Columns"

Y ahora estoy intentando eso …

Parece que está buscando una solución en DAX.

Según las sugerencias de @Sean, puede intentar ajustar «TRUE» a TRUE () y «FALSE» a FALSE ().

Si esto no ayuda y si una solución en Power Query también le parece bien, puedo echarle un vistazo más de cerca hoy.

Sólo házmelo saber,

Opté por DAX porque pensé que podría encontrar una solución con él, pero no lo hice … Entonces, si logras encontrar una especie de solución, ¡sería genial! Intenté también creando algunas columnas calculadas pero no funcionó. El problema es que hay muchas versiones por proyecto …

Avísame si encuentras la manera.

¡Muchas gracias por ayudar!

Los criterios exactos de verdadero o falso aún no están claros para mí, pero lo intenté.

Creé un archivo de Excel con los datos.

En Power BI, creé el código de Power Query a continuación. Incluye explicaciones (las líneas que comienzan con //).

Puede copiar el código: en Power BI, elija «Obtener datos» – Consulta en blanco – Editor avanzado y reemplace el código predeterminado con el código siguiente. Ajuste la fuente de datos a la suya, elija «Listo» y luego, en la pestaña Inicio, elija Cerrar y cargar.

También creé este video que lo lleva a través de los pasos de la consulta. No es una grabación en vivo de la creación de la consulta, sino un tutorial después de que creé la consulta.

Posiblemente aún se requiera algún ajuste detallado, pero esto debería estar cerca del resultado final.

let
    // Next 3 steps are created when importing the data from Excel
    Source = Excel.Workbook(File.Contents("C:UsersMarcelDocumentsForum bijdragenPower BI CommunityPower Query Syntax - Project status changes.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"PROJECT ID", Int64.Type}, {"STATUS", type text}, {"DATE_VERSION", type text}}),

    // Add Index for the original sort so the data can be sort back at the end
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Original Sort", 1, 1),

    // Add column with real date from [DATE VERSION]. "nl-NL" is used so format DD-MM-YYYY is recognized. 31-12-9999 for "LIVE", so it will sort at the end
    #"Added Custom" = Table.AddColumn(#"Added Index", "Version_Date", each if [DATE_VERSION] = "LIVE" then #date(9999,12,31) else Date.From(Text.Replace([DATE_VERSION],"_","-"),"nl-NL"), type date),

    // Sort on Project and date
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"PROJECT ID", Order.Ascending}, {"Version_Date", Order.Ascending}}),

    // Add 2 indices so the table can be merged with itself, such that the project and status from the previous row will be on the current row
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"},"Previous",JoinKind.LeftOuter),

    // After the merge, we need the previous project ID and the previous status
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"PROJECT ID", "STATUS"}, {"Previous.PROJECT ID", "Previous.STATUS"}),

    // Add a column with true if: the current version = "LIVE" and the Project ID = Previous Project ID and the Status <> Previous Status, else false 
    #"Added Custom1" = Table.AddColumn(#"Expanded Previous", "LIVE Status <> Previous Status?", each [DATE_VERSION] = "LIVE" and [PROJECT ID] = [Previous.PROJECT ID] and [STATUS] <> [Previous.STATUS], type logical),

    // Sort back to the original sort
    #"Sorted Rows1" = Table.Sort(#"Added Custom1",{{"Original Sort", Order.Ascending}}),

    // Remove columns that are no longer required
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Original Sort", "Version_Date", "Index", "Index.1", "Previous.PROJECT ID", "Previous.STATUS"})
in
    #"Removed Columns"

Gracias @MarcelBeug, ¡eso es realmente útil!

Problema resuelto 🙂

¡Que tenga un lindo día!

Por ejemplo, estoy intentando eso, pero definitivamente no es la forma correcta. Primero, eso no funciona. En segundo lugar, eso sí tiene en cuenta la variación de un proyecto en particular entre dos fechas / versiones.

Gracias a todos ! 🙂 @Sean @Anónimo @MarcelBeug

Ayuda, pero tal vez no fui muy claro en mi solicitud inicial. Es un poco más complicado: tengo una versión de varias fechas (no solo diciembre, sino todos los meses de muchos años), así que quiero obtener un VERDADERO cuando, por ejemplo, hay una variación de estado para el mismo proyecto entre dos versiones (elijo una comparación entre diciembre de 2016 y hoy = EN VIVO). Algo como :

SI (Project_ID = 123 & Version = LIVE & Status = CLOSED) AND (Project_ID = 123 & Version = 01_12_2016 & Status = OPEN) ENTONCES VERDADERO

ELSE FALSE

Pero realmente no sé cómo manejar estos múltiples Y …

Anónimo

Hola @ sophie63

No estoy seguro de haber cumplido bien sus requisitos.

Aquí hay un código para una columna agregada. El código comprueba, si [STATUS] está cerrado o no.

Puede cambiar el código según sus necesidades.

let
    YOUR_LAST_STEP = THE_LAST_LINE_OF_CODE_YOU_WROTE

    #"Added Custom" = Table.AddColumn(YOUR_LAST_STEP, "CLOSED_2017", each if [STATUS] ="CLOSED" then "TRUE" else "FALSE")
in
    #"Added Custom"

El Editor de consultas tiene una opción de columna condicional aún más fácil … en la pestaña Agregar columna

Entonces lo anterior generará su código M

Sin embargo, si desea que esto se haga con DAX

CLOSED_2017 DAX = IF ( 'Table'[STATUS] = "CLOSED", "TRUE", "FALSE")

También puede usar las funciones VERDADERO () y FALSO () arriba … no las ponga entre comillas

CLOSED_2017 DAX = IF ( 'Table'[STATUS] = "CLOSED", TRUE () , FALSE () )

¡Espero que esto ayude!

Anónimo

Una pregunta, tres soluciones … 😉

¡Eso es Power Query!

Caballeros @Anonymous y @Sean

No es solo Power Query, también DAX.
Al usar la funcionalidad «Agregar columna condicional», obtiene «VERDADERO» https://community.powerbi.com/ «FALSO», no las lógicas verdadero / falso (a menos que los parámetros estén definidos para VERDADERO y FALSO y seleccionados en los campos de valores en la pantalla para agregar una columna condicional).

Anónimo

@MarcelBeugand @Sean

1. tienes razón, Marcel

2. la solución debe depender de los requisitos del cliente (booleanos o de texto). No lo sabemos por el momento.

Ella lo sabe y nos lo hará 😉

@MarcelBeug

Mi segunda medida DAX devuelve booleano

Los requisitos tampoco son claros para mí, pero de todos modos esperaría que el resultado fuera un verdadero o falso lógico, no un texto.

Entonces, si «CERRADO» significa verdadero y, de lo contrario, falso, ajustaría el código de @Anónimo a:

let
    YOUR_LAST_STEP = THE_LAST_LINE_OF_CODE_YOU_WROTE

    #"Added Custom" = Table.AddColumn(YOUR_LAST_STEP, "CLOSED_2017", each [STATUS] ="CLOSED")
in
    #"Added Custom"

Pero supongo que los requisitos serán más complicados a medida que obtengamos más explicaciones y ejemplos de @ sophie63

Entradas relacionadas

Deja un comentario Cancelar respuesta