Microsoft Excel - Power Query - Percorso relativo

Scenario

Sul Desktop abbiamo la cartella Power_Query_Percorso_relativo

contenente due file di Excel

• dati.xlsx
• excel_power_query.xlsx

Quando nel file excel_power_query.xlsx creiamo la connessione a dati.xlsx viene generato il codice

Come si può notare nella riga 2, viene fatto riferimento al percorso assoluto dove si trova il file dei dati, cioè

"C:\Users\info\Desktop\Power_Query_Percorso_relativo\dati.xlsx"

Questo crea un problema quando passo la cartella su un altro computer.

Anche semplicemente s spostiamo la cartella  Power_Query_Percorso_relativo dal Desktop in Documenti, quando apriamo il file excel_power_query.xlsx, si ha l'errore:

[DataSource.Error] Impossibile trovare una parte del percorso

1° Soluzione - Impostazioni origine dati

Possiamo correggere l'errore con gli strumenti di Excel modificando la connessione.

Nella scheda Home andiamo su Imposta origine dati e facciamo click su Cambia origine...

Facendo click su Sfoglia possiamo impostare il nuovo Percorso File

Una volta selezionato il file nella nuova posizione e confermato con apri

Confermiamo con OK il nuovo percorso

E infine

Per correggere la segnalazione di errore

occorre fare click su Aggiorna anteprima

2° Soluzione - utilizzo dell'Editor avanzato

Dal menù Visualizza facciamo click su Editor avanzato.

Possiamo modificare direttamente il percorso inserendo quello corretto

Una volta salvato con Fatto, la tabella verrà ricaricata correttamente.

3° Soluzione - utilizzo del percorso relativo

Le due soluzioni precedenti presentano i seguenti svantaggi:

• Se il foglio excel_power_query.xlsx contiene numerose connessioni a file esterni, occorrerà eseguire molte modifiche
• se invio la cartella con tutti i file (dati e query), chi la riceve deve essere in grado di applicare le correzioni sul percorso altrimenti non può utilizzare il materiale ricevuto

L'idea è quindi di avere una soluzione flessibile che utilizzi un percorso relativo per trovare i files contenenti i dati.

Per fare questo utilizziamo la funzione =CELLA(). Se come promo argomento mettiamo "nomefile" possiamo avere informazioni su dove è memorizzato il nostro file.

Creiamo la pagina Per fare questo utilizziamo la funzione =CELLA(). Se come promo argomento mettiamo "nomefile" possiamo avere informazioni su dove è memorizzato il nostro file..

Nella cella A1 inseriamo la formula

=CELLA("nomefile";$A$1)

come risultato abbiamo una stringa che contiene anche il percorso assoluto dove è salvato il nostro file,

Per informazioni sulla funzione CELLA consultare il manuale online https://support.microsoft.com

Quello che ci serve però è la stringa fino al carattere [ . Per fare questo possiamo utilizzare la funzione =TROVA(). Con

=TROVA("[";CELLA("filename";$A$1);1)

abbiamo la posizione del carattere [ all'interno della stringa:

Non ci rimane che usare la funzione SINISTRA() per estrarre il percorso del file, basandoci sulla posizione del carattere [ calcolata prima, sottratta di 1

Possiamo scrivere direttamente in A1

=SINISTRA(CELLA("filename";$A$1);TROVA("[";CELLA("filename";$A$1);1)-1)

Rimanendo sempre nella A1 creiamo il nome path (etichetta) per questa cella. Un modo è dal menù Formule > Definisci nome.

Ora possiamo usare questa informazione nell'editor avanzato delle Query

Definiamo la variabile Percorso tramite l'istruzione Excel.CurrentWorkbook()

    Percorso = Excel.CurrentWorkbook(){[Name="path"]}[Content][Column1]{0},

che utilizziamo nella riga successiva

    Origine = Excel.Workbook(File.Contents(Percorso & "dati.xlsx"), null, true),

Il codice diventa

Facendo click su Fatto l'errore scompare

Test

Spostiamo a questo punto la cartella Power_Query_Percorso_relativo dal Desktop nella cartella Documents\CORSO PC

Riapriamo il file excel_power_query.xlsx. Aggiorniamo e come si può notare non ci sono errori 😀

In allegato la cartella contenente i file utilizzati