Crear fórmulas de Power Query en Excel

La Editor de Power Query proporciona una experiencia de consulta de datos y modelado para Excel que puede usar para cambiar la forma de los datos de muchos orígenes de datos. Para mostrar la ventana Editor de Power Query, importe datos de orígenes de datos externosen una hoja de cálculo de Excel, seleccione una celda de los datos y, a continuación, seleccione Consulta > Editar. A continuación se muestra un resumen de los componentes principales.

1. La Editor de Power Query cinta de opciones que usa para dar forma a los datos

2. El panel Consultas que usa para buscar orígenes de datos y tablas

3. Menús contextuales que son métodos abreviados de teclado cómodos para los comandos de la cinta de opciones

4. Vista previa de datos que muestra los resultados de los pasos aplicados a los datos

5. El panel Configuración de consulta que muestra las propiedades y cada paso de la consulta

En segundo plano, cada paso de una consulta se basa en una fórmula visible en la barra de fórmulas.

Puede haber ocasiones en las que desee modificar o crear una fórmula. Las fórmulas usan el Power Query lenguaje de fórmulas, que puede usar para crear expresiones simples y complejas. Para obtener más información sobre la sintaxis, los argumentos, las observaciones, las funciones y los ejemplos, vea Power Query lenguaje de fórmulas M.

Usando una lista de campeonatos de fútbol como ejemplo, usa Power Query para tomar datos sin procesar que has encontrado en un sitio web y convertirlos en una tabla con formato correcto. Vea cómo se crean los pasos de la consulta y las fórmulas correspondientes para cada tarea en el panel Configuración de consulta en Pasos aplicados y en la barra de fórmulas.

Procedimiento

1. Para importar los datos, selecciona Datos > Desde la Web, escribe "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" en el cuadro URL y, a continuación, selecciona Aceptar.

2. En el cuadro de diálogo Navegador , seleccione la tabla Resultados [Editar] de la izquierda y, a continuación, seleccione Transformar datos en la parte inferior. Aparecerá el editor de Power Query.

3. Para cambiar el nombre de consulta predeterminado, en el panel Configuración de consulta , en Propiedades, elimine "Resultados [Editar]" y, a continuación, escriba "campos de la UEFA".

4. Para quitar las columnas no deseadas, seleccione la primera, la cuarta y la quinta columna y, a continuación, seleccione Inicio > Quitar columna > Quitar otras columnas.

5. Para quitar los valores no deseados, seleccione Columna1, seleccione Inicio > Reemplazar valores, escriba "detalles" en el cuadro Valores para buscar y, después, seleccione Aceptar.

6. Para quitar las filas que contienen la palabra "Año", seleccione la flecha de filtro en Columna1, desactive la casilla situada junto a "Año" y, después, seleccione Aceptar.

7. Para cambiar el nombre de los encabezados de columna, haga doble clic en cada uno de ellos y, a continuación, cambie "Columna1" a "Año", "Columna4" por "Ganador" y "Columna5" por "Puntuación final".

8. Para guardar la consulta, seleccione Inicio > Cerrar & Cargar.

Resultado

La tabla siguiente es un resumen de cada paso aplicado y la fórmula correspondiente.

Paso de consulta y tarea	Fórmula
Origen Conectarse a un origen de datos web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navegación Seleccionar la tabla para conectarse	=Source{2}[Data]
Tipo cambiado Cambiar tipos de datos (lo que Power Query hace automáticamente)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Otras columnas quitadas Eliminar otras columnas para mostrar únicamente las columnas de interés	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Valor reemplazado Reemplazar valores para limpiar los valores de una columna seleccionada	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filas filtradas Filtrar valores en una columna	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Columnas con nombre cambiado Encabezados de columna cambiados para que sean significativos	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Importante    Tenga cuidado al editar los pasos Origen, Navegación  y Tipo cambiado porque se crean por Power Query definir y configurar el origen de datos.

Mostrar u ocultar la barra de fórmulas

La barra de fórmulas se muestra de forma predeterminada, pero si no está visible, puede volver a mostrarla.

• Seleccione Ver diseño > > barra de fórmulas.

Crearuna fórmula en la barra de fórmulas

1. Para abrir una consulta, busque una cargada previamente desde la Editor de Power Query, seleccione una celda de los datos y, a continuación, seleccione Consulta > Editar. Para obtener más información , vea Crear, cargar o editar una consulta en Excel.

2. En el panel Configuración de consulta , en Pasos aplicados, seleccione el paso que desea editar.

3. En la barra de fórmulas, busque y cambie los valores de los parámetros y, después, seleccione el icono Entrar o presione Entrar. Por ejemplo, cambie esta fórmula para mantener también Columna2:
   
   Antes: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Después:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Seleccione el icono entrar o presione Entrar para ver los nuevos resultados que se muestran en la Vista previa de datos.

5. Para ver el resultado en una hoja de cálculo de Excel, seleccione Inicio > Cerrar & Cargar.

Crear una fórmula en la barra de fórmulas

Para obtener un ejemplo de fórmula simple, vamos a convertir un valor de texto en mayúsculas y minúsculas con la función Texto.Correcto.

1. Para abrir una consulta en blanco, en Excel seleccione Datos > Obtener datos > de otros orígenes > consulta en blanco. Para obtener más información , vea Crear, cargar o editar una consulta en Excel.

2. En la barra de fórmulas, escriba=Text.Proper("text value")y, después, seleccione el icono Entrar o presione Entrar.
   
   Los resultados se muestran en Vista previa de datos.

3. Para ver el resultado en una hoja de cálculo de Excel, seleccione Inicio > Cerrar & Cargar.

Resultado:

 Al crear una fórmula, Power Query valida la sintaxis de la fórmula. Sin embargo, al insertar, reordenar o eliminar un paso intermedio de una consulta, es posible que se rompa una consulta.  Compruebe siempre los resultados en Vista previa de datos.

Importante    Tenga cuidado al editar los pasos Origen, Navegación  y Tipo cambiado porque se crean por Power Query definir y configurar el origen de datos.

Editar una fórmula mediante un cuadro de diálogo

Este método hace uso de cuadros de diálogo que varían según el paso. No necesita conocer la sintaxis de la fórmula.

1. Para abrir una consulta, busque una cargada previamente desde la Editor de Power Query, seleccione una celda de los datos y, a continuación, seleccione Consulta > Editar. Para obtener más información , vea Crear, cargar o editar una consulta en Excel.

2. En el panel Configuración de consulta , en Pasos aplicados, seleccione el icono Editar configuración del paso que desea editar o haga clic con el botón derecho en el paso y, después, seleccione Editar configuración.

3. En el cuadro de diálogo, realice los cambios y, después, seleccione Aceptar.

Insertar un paso

Después de completar un paso de consulta que cambia la forma de los datos, se agrega un paso de consulta debajo del paso de consulta actual. pero al insertar un paso de consulta en mitad de los pasos, puede producirse un error en los pasos siguientes. Power Query muestra una advertencia de Insertar paso cuando intenta insertar un paso nuevo y el nuevo paso modifica campos, como los nombres de columna, que se usan en cualquiera de los pasos que siguen al paso insertado.

1. En el panel Configuración de consulta , en Pasos aplicados, seleccione el paso que desea que preceda inmediatamente al nuevo paso y su fórmula correspondiente.

2. Seleccione el icono Agregar paso a la izquierda de la barra de fórmulas. Como alternativa, haga clic con el botón derecho en un paso y, después, seleccione Insertar paso después. Se crea una nueva fórmula en el formato :
   
   = <nameOfTheStepToReference>, como =Production.WorkOrder.

3. Escriba la nueva fórmula con el formato:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Por ejemplo, supongamos que tiene una tabla con la columna Género y desea agregar una columna con el valor "Ms". o "Sr.", dependiendo del sexo de la persona. La fórmula sería:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Reordenar un paso

• En el panel Configuración de consultas , en Pasos aplicados, haga clic con el botón derecho en el paso y, después, seleccione Subir o Bajar.

Eliminar paso

• Seleccione el icono Eliminar a la izquierda del paso o haga clic con el botón derecho en el paso y, después, seleccione Eliminar o Eliminar hasta el final. El icono Eliminar también está disponible a la izquierda de la barra de fórmulas.

En este ejemplo, vamos a convertir el texto de una columna en mayúsculas y minúsculas con una combinación de fórmulas en la Editor avanzado. 

Por ejemplo, tiene una tabla de Excel, denominada Pedidos, con una columna ProductName que desea convertir a nombre propio. 

Antes:

Después:

Cuando crea una consulta avanzada, crea una serie de pasos para la fórmula de consulta en función de la expresión let. Use la expresión let para asignar nombres y calcular valores a los que hace referencia la cláusula in , que define el paso. Este ejemplo devuelve el mismo resultado que el de la sección "Crear una fórmula en la barra de fórmulas".

let  
    Source = Text.Proper("hello world")
in  
    Source  

Verá que cada paso se basa en un paso anterior haciendo referencia a un nombre paso a paso. A modo de recordatorio, el Power Query Formula Language distingue mayúsculas de minúsculas.

Fase 1: Abrir el Editor avanzado

1. En Excel, seleccione Datos > Obtener datos > otros orígenes > consulta en blanco. Para obtener más información , vea Crear, cargar o editar una consulta en Excel.

2. En la Editor de Power Query, seleccione Inicio > Editor avanzado, que se abre con una plantilla de la expresión let.

Fase 2: Definir el origen de datos

1. Cree la expresión let con la función Excel.CurrentWorkbook de la siguiente manera:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]#x2
   
       
   
   deSource

2. Para cargar la consulta en una hoja de cálculo, seleccione Listo y, a continuación, seleccione Inicio > Cerrar & Cargar > Cerrar & Cargar.

Resultado:

Fase 3: Promover la primera fila a encabezados

1. Para abrir la consulta, en la hoja de cálculo seleccione una celda de los datos y, después, seleccione Consulta > Editar. Para obtener más información, vea Crear, cargar o editar una consulta en Excel (Power Query).

2. En la Editor de Power Query, seleccione Inicio > Editor avanzado, que se abre con la instrucción que creó en fase 2: Definir el origen de datos.

3. En la expresión Let, agregue #"Primera fila como encabezado" y Table.PromoteHeaders funcionan de la siguiente manera:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
       #"First Row as Header"#x3#"First Row as Header" = Table.PromoteHeaders(Source)
   

4. Para cargar la consulta en una hoja de cálculo, seleccione Listo y, a continuación, seleccione Inicio > Cerrar & Cargar > Cerrar & Cargar.

Resultado:

Fase 4: Cambiar cada valor de una columna al formato de nombre propio

1. Para abrir la consulta, en la hoja de cálculo seleccione una celda de los datos y, después, seleccione Consulta > Editar. Para obtener más información , vea Crear, cargar o editar una consulta en Excel.

2. En la Editor de Power Query, seleccione Inicio > Editor avanzado, que se abre con la instrucción que creó en la fase 3: Promover la primera fila a encabezados.

3. En la expresión Let, convierta cada valor de columna ProductName al texto correcto mediante la función Table.TransformColumns, haciendo referencia al paso anterior de la fórmula de consulta "Primera fila como encabezado", agregando #"Poner en mayúsculas cada Word" al origen de datos y, a continuación, asignando #"Poner en mayúsculas cada Word" al resultado in.
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. Para cargar la consulta en una hoja de cálculo, seleccione Listo y, a continuación, seleccione Inicio > Cerrar & Cargar > Cerrar & Cargar.

Resultado:

Puede controlar el comportamiento de la barra de fórmulas en la Editor de Power Query de todos los libros.

Mostrar u ocultar la barra de fórmulas

1. Seleccione Opciones de> de archivo y Configuración > Opciones de consulta.

2. En el panel izquierdo, en GLOBAL, seleccione Editor de Power Query.

3. En el panel derecho, en Diseño, seleccione o desactive Mostrar la barra de fórmulas.

Activar o desactivar M Intellisense

1. Seleccione Opciones de> de archivo y Configuración > Opciones de consulta .

2. En el panel izquierdo, en GLOBAL, seleccione Editor de Power Query.

3. En el panel derecho, en Fórmula, active o desactive Habilitar M Intellisense en la barra de fórmulas, el editor avanzado y el cuadro de diálogo columna personalizada.

Nota    El cambio de esta configuración se aplicará la próxima vez que abras la ventana de Editor de Power Query.