Parametrización de rutas de datos en Power Query

Se xa comezaches a usar as ferramentas do complemento gratuíto Power Query en Microsoft Excel, moi pronto atoparás un problema moi especializado, pero moi frecuente e molesto, asociado á ruptura constante de ligazóns aos datos de orixe. A esencia do problema é que se na súa consulta se refire a ficheiros ou cartafoles externos, Power Query codifica a ruta absoluta a eles no texto da consulta. Todo funciona ben no teu ordenador, pero se decides enviar un ficheiro cunha solicitude aos teus compañeiros, entón estarán decepcionados, porque. teñen un camiño diferente aos datos de orixe no seu ordenador e a nosa consulta non funcionará.

Que facer ante tal situación? Vexamos este caso con máis detalle co seguinte exemplo.

Formulación do problema

Supoñamos que temos no cartafol E:Informes de vendas atópase o arquivo Os 100 mellores produtos.xls, que é unha carga da nosa base de datos corporativa ou do sistema ERP (1C, SAP, etc.) Este ficheiro contén información sobre os produtos máis populares e ten o seguinte aspecto:

Probablemente estea claro desde o primeiro momento que é case imposible traballar con el en Excel desta forma: as filas baleiras a través dunha con datos, celas combinadas, columnas adicionais, un encabezado multinivel, etc. interferirán.

Polo tanto, xunto a este ficheiro no mesmo cartafol, creamos outro novo ficheiro Handler.xlsx, na que crearemos unha consulta de Power Query que cargará datos feos do ficheiro de carga de orixe Os 100 mellores produtos.xls, e ordenalos:

Facendo unha solicitude a un ficheiro externo

Abrindo o ficheiro Handler.xlsx, seleccione na pestana Data Mando Obter datos - Do ficheiro - Desde o libro de traballo de Excel (Datos — Obter datos — Do ficheiro — Desde Excel), despois especifique a localización do ficheiro fonte e a folla que necesitamos. Os datos seleccionados cargaranse no editor de Power Query:

Devolvémolos á normalidade:

1. Elimina liñas baleiras con Inicio — Eliminar liñas — Eliminar liñas baleiras (Inicio — Eliminar filas — Eliminar filas baleiras).
2. Elimina as catro liñas principais innecesarias Inicio — Eliminar filas — Eliminar filas principais (Inicio — Eliminar filas — Eliminar filas superiores).
3. Levante a primeira fila á cabeceira da táboa co botón Use a primeira liña como cabeceiras aba casa (Inicio — Usa a primeira fila como cabeceira).
4. Separa o artigo de cinco díxitos do nome do produto na segunda columna usando o comando columna dividida aba Transformación (Transformar — Dividir columna).
5. Elimina as columnas innecesarias e renomea os títulos das restantes para unha mellor visibilidade.

Como resultado, deberíamos obter a seguinte imaxe, moito máis agradable:

Queda por cargar de novo esta táboa ennobrecida á folla do noso arquivo Handler.xlsx o equipo pechar e descargar (Inicio — Pechar e cargar) aba casa:

Buscar a ruta a un ficheiro nunha solicitude

Agora vexamos como se ve a nosa consulta "debaixo do capó", na linguaxe interna integrada en Power Query co nome conciso "M". Para iso, volva á nosa consulta facendo dobre clic nela no panel dereito Solicitudes e conexións e na pestana revisión escoller Editor avanzado (Ver — Editor avanzado):

Na xanela que se abre, a segunda liña revela inmediatamente un camiño codificado para o noso ficheiro de carga orixinal. Se podemos substituír esta cadea de texto por un parámetro, variable ou ligazón a unha cela da folla de Excel onde este camiño estea previamente escrito, poderemos cambialo facilmente máis tarde.

Engade unha táboa intelixente cunha ruta de ficheiro

Pechemos Power Query polo momento e volvamos ao noso ficheiro Handler.xlsx. Engademos unha nova folla baleira e fagamos unha pequena táboa "intelixente", na única cela da cal se escribirá o camiño completo ao noso ficheiro de datos de orixe:

Para crear unha táboa intelixente a partir dun rango normal, pode usar o atallo de teclado Ctrl+T ou botón Formato como táboa aba casa (Inicio — Formato como táboa). O título da columna (cela A1) pode ser absolutamente calquera cousa. Teña en conta tamén que para claridade dei un nome á táboa Parámetros aba Construtor (Deseño).

Copiar un camiño desde o Explorer ou incluso introducilo manualmente, por suposto, non é especialmente difícil, pero o mellor é minimizar o factor humano e determinar o camiño, se é posible, automaticamente. Isto pódese implementar usando a función estándar de folla de cálculo de Excel CÉLULA (CELULA), que pode proporcionar unha morea de información útil sobre a cela especificada como argumento, incluíndo o camiño ao ficheiro actual:

Se asumimos que o ficheiro de datos de orixe sempre se atopa no mesmo cartafol que o noso procesador, entón o camiño que necesitamos pode estar formado pola seguinte fórmula:

=LEFT(CELL(“nome do ficheiro”);FIND(“[“;CELULA(“nome do ficheiro”)-1)&”Os 100 produtos principais.xls”

ou en versión inglesa:

=LEFT(CELL(«nome do ficheiro»);FIND(«[«;CELA(«nome do ficheiro»))-1)&»Топ-100 товаров.xls»

… onde está a función LEVSIMV (ESQUERDA) leva un anaco de texto desde a ligazón completa ata o corchete de apertura (é dicir, o camiño ao cartafol actual) e, a continuación, pégase o nome e a extensión do noso ficheiro de datos de orixe.

Parametriza o camiño na consulta

O último e máis importante toque queda: escribir o camiño ao ficheiro de orixe na solicitude Os 100 mellores produtos.xls, referíndose á cela A2 da nosa táboa "intelixente" creada Parámetros.

Para iso, volvamos á consulta de Power Query e abramos de novo Editor avanzado aba revisión (Ver — Editor avanzado). No canto dun camiño de cadea de texto entre comiñas "E: Informes de vendasTop 100 products.xlsx" Imos presentar a seguinte estrutura:

Excel.CurrentWorkbook(){[Name="Configuración"]}[Contido]0 {}[Ruta aos datos fonte]

Vexamos en que consiste:

• Excel.CurrentWorkbook() é unha función da linguaxe M para acceder ao contido do ficheiro actual
• {[Name="Configuración"]}[Contido] – este é un parámetro de refinamento para a función anterior, que indica que queremos obter o contido da táboa "intelixente" Parámetros
• [Ruta aos datos fonte] é o nome da columna da táboa Parámetrosao que nos referimos
  
• 0 {} é o número de fila da táboa Parámetrosdo que queremos sacar datos. O límite non conta e a numeración comeza a partir de cero, non de un.

Iso é todo, de feito.

Queda por premer Rematar e comproba como funciona a nosa solicitude. Agora, ao enviar o cartafol completo cos dous ficheiros dentro a outro PC, a solicitude permanecerá operativa e determinará automaticamente o camiño aos datos.

• Que é Power Query e por que é necesario cando se traballa en Microsoft Excel
• Como importar un fragmento de texto flotante en Power Query
• Rediseñar unha táboa cruzada XNUMXD nunha táboa plana con Power Query