Parametrizzazione dei percorsi di dati in Power Query

Se hai già iniziato a utilizzare gli strumenti del componente aggiuntivo gratuito Power Query in Microsoft Excel, molto presto incontrerai un problema altamente specializzato, ma molto frequente e fastidioso associato all'interruzione costante dei collegamenti ai dati di origine. L'essenza del problema è che se nella tua query fai riferimento a file o cartelle esterni, Power Query codifica il percorso assoluto per essi nel testo della query. Tutto funziona bene sul tuo computer, ma se decidi di inviare un file con una richiesta ai tuoi colleghi, saranno delusi, perché. hanno un percorso diverso per i dati di origine sul loro computer e la nostra query non funzionerà.

Cosa fare in una situazione del genere? Esaminiamo questo caso in modo più dettagliato con il seguente esempio.

Formulazione del problema

Supponiamo di avere nella cartella E:Vendite rapporti mente il file I 100 migliori prodotti.xls, che è un caricamento dal nostro database aziendale o dal sistema ERP (1C, SAP, ecc.). Questo file contiene informazioni sugli articoli di base più popolari e ha il seguente aspetto:

Probabilmente è subito chiaro che è quasi impossibile lavorarci in Excel in questo modulo: righe vuote con dati, celle unite, colonne extra, un'intestazione multilivello, ecc. Interferiranno.

Pertanto, accanto a questo file nella stessa cartella, creiamo un altro nuovo file Gestore.xlsx, in cui creeremo una query Power Query che caricherà dati brutti dal file di caricamento di origine I 100 migliori prodotti.xls, e mettili in ordine:

Fare una richiesta a un file esterno

Apertura del file Gestore.xlsx, seleziona nella scheda Dati Comando Ottieni dati - Da file - Dalla cartella di lavoro di Excel (Dati — Ottieni dati — Da file — Da Excel), quindi specificare la posizione del file di origine e il foglio di cui abbiamo bisogno. I dati selezionati verranno caricati nell'editor di Power Query:

Riportiamoli alla normalità:

1. Elimina le righe vuote con Home — Elimina righe — Elimina le righe vuote (Home — Rimuovi righe — Rimuovi righe vuote).
2. Elimina le prime 4 righe non necessarie Home — Elimina righe — Elimina le prime righe (Home — Rimuovi righe — Rimuovi le prime righe).
3. Alza la prima riga all'intestazione della tabella con il pulsante Usa la prima riga come intestazioni linguetta Casa (Home — Usa la prima riga come intestazione).
4. Separare l'articolo a cinque cifre dal nome del prodotto nella seconda colonna utilizzando il comando colonna divisa linguetta Trasformazione (Trasforma — Dividi colonna).
5. Elimina le colonne non necessarie e rinomina le intestazioni di quelle rimanenti per una migliore visibilità.

Di conseguenza, dovremmo ottenere la seguente immagine molto più piacevole:

Resta da caricare di nuovo questa tabella nobilitata sul foglio nel nostro file Gestore.xlsx la squadra chiudi e scarica (Home — Chiudi&Carica) linguetta Casa:

Trovare il percorso di un file in una richiesta

Ora vediamo come appare la nostra query "sotto il cofano", nel linguaggio interno integrato in Power Query con il nome conciso "M". Per fare ciò, torna alla nostra query facendo doppio clic su di essa nel riquadro di destra Richieste e connessioni e nella scheda Review scegliere Editor avanzato (Visualizza — Editor avanzato):

Nella finestra che si apre, la seconda riga rivela immediatamente un percorso hardcoded al nostro file di caricamento originale. Se possiamo sostituire questa stringa di testo con un parametro, una variabile o un collegamento a una cella di un foglio di Excel in cui questo percorso è pre-scritto, possiamo modificarlo facilmente in un secondo momento.

Aggiungi una tabella intelligente con un percorso di file

Chiudiamo per ora Power Query e torniamo al nostro file Gestore.xlsx. Aggiungiamo un nuovo foglio vuoto e creiamo su di esso una piccola tabella "intelligente", nell'unica cella di cui verrà scritto il percorso completo del nostro file di dati di origine:

Per creare una tabella intelligente da un intervallo regolare, puoi utilizzare la scorciatoia da tastiera Ctrl+T o pulsante Formatta come una tabella linguetta Casa (Home — Formatta come tabella). L'intestazione di colonna (cella A1) può essere qualsiasi cosa. Si noti inoltre che per chiarezza ho assegnato un nome alla tabella parametri linguetta Costruttore (Design).

Copiare un percorso da Explorer o anche inserirlo manualmente non è, ovviamente, particolarmente difficile, ma è meglio ridurre al minimo il fattore umano e determinare il percorso, se possibile, automaticamente. Questo può essere implementato utilizzando la funzione standard del foglio di lavoro di Excel CELL (CELLULA), che può fornire un sacco di informazioni utili sulla cella specificata come argomento, incluso il percorso del file corrente:

Se assumiamo che il file di dati di origine si trovi sempre nella stessa cartella del nostro Processor, il percorso di cui abbiamo bisogno può essere formato dalla seguente formula:

=LEFT(CELL("nomefile");TROVA("[";CELL("nomefile"))-1)&"Top 100 products.xls"

o in versione inglese:

=LEFT(CELL(«nomefile»);TROVA(«[«;CELL(«nomefile»))-1)&»Топ-100 товаров.xls»

... dov'è la funzione LEVSIMV (SINISTRA) prende un pezzo di testo dal collegamento completo fino alla parentesi quadra di apertura (cioè il percorso della cartella corrente), e quindi il nome e l'estensione del nostro file di dati di origine vengono incollati su di esso.

Parametrizzare il percorso nella query

Rimane l'ultimo e più importante tocco: scrivere il percorso del file di origine nella richiesta I 100 migliori prodotti.xls, facendo riferimento alla cella A2 della nostra tabella "intelligente" creata parametri.

Per fare ciò, torniamo alla query di Power Query e apriamola di nuovo Editor avanzato linguetta Review (Visualizza — Editor avanzato). Invece di un percorso di stringa di testo tra virgolette "E: Rapporti sulle vendite Top 100 products.xlsx" Introduciamo la seguente struttura:

Excel.CurrentWorkbook(){[Nome=”Impostazioni”]}[Contenuto]0 {}[Percorso dei dati di origine]

Vediamo in cosa consiste:

• Excel.CurrentWorkbook() è una funzione del linguaggio M per accedere al contenuto del file corrente
• {[Nome=”Impostazioni”]}[Contenuto] – questo è un parametro di raffinamento alla funzione precedente, indicando che vogliamo ottenere il contenuto della tabella “intelligente”. parametri
• [Percorso dei dati di origine] è il nome della colonna nella tabella parametria cui ci riferiamo
  
• 0 {} è il numero di riga nella tabella parametrida cui vogliamo prelevare i dati. Il tappo non conta e la numerazione parte da zero, non da uno.

Questo è tutto, in effetti.

Resta da cliccare Fine e controlla come funziona la nostra richiesta. Ora, quando si invia l'intera cartella con entrambi i file all'interno su un altro PC, la richiesta rimarrà operativa e determinerà automaticamente il percorso dei dati.

• Che cos'è Power Query e perché è necessario quando si lavora in Microsoft Excel
• Come importare uno snippet di testo mobile in Power Query
• Riprogettazione di una tabella incrociata XNUMXD in una tabella piatta con Power Query