Aggiunta di più fogli in Excel a LuckyTemplates

Questo tutorial illustrerà come importare e aprire un file Excel con più fogli in una tabella di LuckyTemplates.

Questo file Excel di dati verrà utilizzato per questo tutorial.

Il file contiene 60 fogli di dati con lo stesso layout.

L'obiettivo di questa esercitazione è caricare tutti i fogli Excel in LuckyTemplates e recuperare le informazioni su Bond come un'unica tabella. Devi ottenere il ticker Sicurezza e aggiungerlo in una colonna separata. Quindi, aggiungili tutti insieme in un'unica tabella che sfrutta l'interfaccia utente per generare il codice di cui hai bisogno.

Sommario

• Importazione dei fogli Excel in LuckyTemplates
• Tirare fuori il ticker di sicurezza da Excel a LuckyTemplates
• Rimozione di righe vuote
• Duplicazione di una query
• Assegnazione di tipi di dati alle colonne
• Aggiunta della colonna del ticker di sicurezza da Excel a LuckyTemplates
• Creazione di una colonna personalizzata per la query Bond
• Conclusione

Importazione dei fogli Excel in LuckyTemplates

La prima cosa da fare è aprire l' editor di Power Query e creare un parametro per archiviare il percorso del file utilizzando l' opzione Gestisci parametri .

All'interno della casella Manage Parameters, fare clic su New e immettere FileLocation come nome del parametro. Successivamente, imposta Tipo di dati su Testo e Valori suggeriti su Qualsiasi valore . Copia il percorso del file Excel e incollalo nei campi Valore corrente.

Dopo aver premuto OK, fare clic su Nuova origine e selezionare Cartella di lavoro Excel .

Successivamente, seleziona e apri il file Excel. Al termine, la schermata Navigator si aprirà ed elencherà tutti i fogli all'interno del file Excel.

Selezionare il primo foglio e premere OK. Successivamente, vedrai la tabella all'interno dell'editor di Power Query.

La prossima cosa da fare è cambiare il percorso del file codificato nel parametro del file. Apri la finestra Editor avanzato e modifica il percorso del file nel codice sorgente in FileLocation .

È preferibile avere i dati nelle tabelle di Excel perché i limiti o l'intervallo di dati vengono definiti prima di inserirli in Power Query . Poiché il file contiene fogli e nessuna tabella, corre il rischio di inserire colonne e righe vuote, quindi è necessario risolverlo.

Tirare fuori il ticker di sicurezza da Excel a LuckyTemplates

Sapere che il layout dei fogli è fisso aiuta a costruire una soluzione, soprattutto se si desidera massimizzare e utilizzare l'interfaccia utente per generare il codice. Ad esempio, un requisito è aggiungere una colonna che contenga il ticker Sicurezza. Se guardi i dati, puoi vedere il ticker.

L'interfaccia utente può aiutarti a estrarre il valore del ticker. Fare clic con il tasto destro sulla cella e selezionare Aggiungi come nuova query .

Nella barra della formula, vedrai che il nome della tabella è seguito da un numero di riga in base zero tra parentesi graffe. Le parentesi graffe sono chiamate operatori di indice posizionale . Vedrai anche il nome del campo racchiuso tra parentesi quadre che sono chiamate operatori di accesso al campo .

Con quei codici, ora puoi estrarre il valore. Torna alla tabella Bond e ottieni i dati Bond. Innanzitutto, rimuovi le prime 8 otto righe. Fai clic su Rimuovi righe e seleziona Rimuovi righe superiori .

Successivamente, inserisci 8 nel campo Numero di righe e premi OK.

Quindi, fare clic su Usa prima riga come intestazioni per impostare le intestazioni.

Una volta fatto, il tavolo Bond ora avrà questo aspetto.

Direttamente sotto l'intestazione, vedrai una linea sottile che rappresenta la qualità della colonna. Da lì, puoi vedere che ci sono un certo numero di spazi vuoti nelle colonne. Ciò significa che il file ha portato molte righe vuote.

Rimozione di righe vuote

Per rimuovere le righe vuote, fai clic su Rimuovi righe e seleziona Rimuovi righe vuote .

Tale trasformazione genera questa sintassi:

Record.FieldValues ​​ottiene tutti i valori dalla riga corrente nella tabella come un elenco. List.RemoveMatchingItems rimuove tutti i valori nel primo elenco che hanno una corrispondenza nel secondo elenco. Il secondo elenco contiene solo una stringa di testo vuota o null. Questi sono i valori che verranno esclusi dal primo elenco.

Se tutte le stringhe di testo vuote e null sono state rimosse dall'elenco con i valori del campo record, l'elenco dovrebbe essere vuoto e List.IsEmpty restituirà True. Quindi, Table.SelectRows manterrà i Trues.

Non dovresti finire con una tabella con solo righe vuote. Ecco perché la parola chiave not viene aggiunta prima di List.IsEmpty . Ciò restituisce quindi una tabella contenente righe non vuote.

Oltre alle righe vuote, è necessario rimuovere anche le colonne vuote. Ma prima, dai un'occhiata a cosa genera Power Query quando rimuovi una colonna. Seleziona la quarta colonna e fai clic su Rimuovi colonne .

Dopo aver eseguito la trasformazione, vedrai questa sintassi nella barra della formula.

Chiama la funzione Table.RemoveColumns , quindi fa riferimento e passa il passaggio precedente nel riquadro Passaggi applicati come primo argomento. La trasformazione passa inoltre un elenco contenente il nome della colonna delle colonne che si desidera rimuovere.

Duplicazione di una query

Ora, duplica la query e seleziona il passaggio Promuovi intestazioni nel riquadro Passaggi applicati. Successivamente, fai clic con il pulsante destro del mouse su quel passaggio e seleziona Elimina fino alla fine .

Ricorda che puoi utilizzare l'operatore di indice posizionale per passare un numero di riga in base zero in una serie di parentesi graffe. Quindi mantieni solo la prima riga inserendo 0 tra due parentesi graffe nella barra della formula.

Una volta fatto, il tavolo Bond sarà simile a questo.

Da lì, riutilizzare una parte della logica creata dal passaggio Righe vuote rimosse per trasformare il record in un elenco e rimuovere il valore nullo. Torna alla query Bond e seleziona il passaggio Rimuovi righe vuote. Successivamente, copia questo codice M.

Torna alla query duplicata e incolla il codice nella barra della formula. Quindi, disponi alcune stringhe per rendere la formula simile a questa.

Ora hai creato un elenco con i nomi delle colonne che desideri conservare. Rinomina la query Column Names .

Quindi, torna alla query Bond. Poiché hai creato una query che contiene tutte le colonne che desideri conservare, devi elencare tutte le colonne che desideri escludere nella funzione Table.RemoveColumns .

Modifica {Column4} con la stessa sintassi che è stata copiata dai passaggi Rimosse righe vuote. Cambia anche {“”, null} in ColumnNames .

Quindi, devi passare un elenco con i nomi effettivi delle colonne dalla tabella Bond. Modificare Record.FieldValues(_) in Table.ColumnNames() . Inserire #"Righe vuote rimosse" all'interno delle parentesi per passare un argomento di riferimento alla tabella.

Assegnazione di tipi di dati alle colonne

La prossima cosa da fare è assegnare tipi di dati appropriati alle colonne. Per la colonna Data, fare clic sull'icona accanto all'intestazione e selezionare Data.

Per le colonne PX_LAST e YLD_YTM_MID, selezionare il tipo di dati Numero decimale.

Con queste tre query, hai creato tutti gli elementi costitutivi che puoi utilizzare per progettare una soluzione che indirizzi tutti i fogli nel file Excel . Per questo, è necessario duplicare la query Bond ed eliminare tutti i passaggi tranne il passaggio Origine nel riquadro Passaggi applicati.

Aggiunta della colonna del ticker di sicurezza da Excel a LuckyTemplates

Nel passaggio Origine, puoi vedere tutti i dati all'interno del file Excel. Invece di creare una query separata per ogni foglio, puoi utilizzare la query Bond e trasformare le tabelle nidificate nella colonna Dati.

Innanzitutto, aggiungi il ticker Sicurezza. Se fai clic sullo spazio bianco all'interno della cella di qualsiasi tabella, vedrai un'anteprima del contenuto di quella tabella.

È necessario creare una logica utilizzando un'istruzione per ottenere il ticker di sicurezza. Vai alla query Column2 e copia l'indirizzo del ticker di sicurezza dalla barra della formula.

Successivamente, torna alla query Bond e aggiungi una colonna personalizzata.

Assegna un nome alla colonna Security Ticker e scrivi il seguente codice M.

La formula ha un'istruzione IF che dice che se la parola Security si trova in Column1, fornirà il valore della cella da Column2 che è adiacente ad essa. In caso contrario, verrà fornito un valore null.

Dopo aver premuto OK, verrà aggiunta alla tabella una nuova colonna con i Ticker di sicurezza.

Fare clic sul pulsante a discesa della colonna Ticker di sicurezza e deselezionare null per rimuovere tutti i valori null nella colonna.

Dopodiché, ti verranno lasciate tutte le informazioni sui Titoli da tutti i fogli. Tutto quello che devi fare è ripetere le trasformazioni eseguite nella query Bond(2) e applicarle alle tabelle nidificate nella colonna Data.

Creazione di una colonna personalizzata per la query Bond

Vai alla query Bond(2), apri la finestra Editor avanzato e copia il seguente codice:

Successivamente, torna alla query Bond e crea un'altra colonna personalizzata. Poiché è necessario applicare più trasformazioni in più passaggi, è necessario utilizzare un'istruzione let . Quindi, inserisci let e incolla il codice dall'editor avanzato.

Quindi, modificare Bond_Sheet in Data per trasformare la tabella nella colonna Data.

Dopo aver premuto OK, verrà aggiunta una nuova colonna nella tabella.

Se fai clic sullo spazio bianco all'interno di qualsiasi cella di quella colonna, vedrai i dati dalla query Bond(2).

Tutto ciò di cui hai bisogno è nelle ultime due colonne della query Bond. Quindi, seleziona entrambe le colonne Ticker di sicurezza e Personalizzato e fai clic su Rimuovi altre colonne dalla barra multifunzione Rimuovi colonne.

Espandi la colonna Personalizzato e deseleziona la casella Usa il nome originale come prefisso.

Una volta fatto, convalida la tabella. Sotto la tabella selezionare l' opzione Profilatura colonna basata sull'intero set di dati .

Dopo tale trasformazione, si verificherà un errore nella colonna Dati.

Quindi esamina l'errore facendo clic sulla colonna Dati e selezionando Mantieni errori nella barra multifunzione Mantieni righe.

Se fai clic sul valore Errore all'interno della colonna Dati, puoi leggere il seguente messaggio di errore:

Per risolvere i problemi, rimuovere innanzitutto il passaggio degli errori mantenuti. Fare clic sulla colonna Dati e selezionare Rimuovi errori dalla barra multifunzione Rimuovi righe.

Quindi, reimposta l'opzione di profilatura della colonna sulle prime 1000 righe. E questo è tutto!

Conclusione

Questa soluzione basata sull'interfaccia utente può aiutarti ad aggiungere più fogli da un file Excel a LuckyTemplates. Invece di creare 60 query separate ed eseguire ripetutamente tutte le trasformazioni, questa soluzione può creare una singola query che esegue tutte le trasformazioni. Utilizza e massimizza questa soluzione per creare un ottimo report di dati .

Melissa