Layout del calendario in formato tabulare utilizzando Power Query

In questa esercitazione imparerai a trasformare più layout di tipo di calendario in un formato tabulare usando Power Query in LuckyTemplates. Imparerai come trasformare più file invece di un singolo file da una cartella. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Questo tutorial ti insegnerà anche come colmare le lacune nei tuoi dati e come raccoglierli in LuckyTemplates.

Sommario

• Preparazione dei file e dei layout in Power Query
• Formulazione di calcoli nella Power Query
• Colmare le lacune nelle date
• Raggruppamento di tabelle separate
• Conclusione

Preparazione dei file e dei layout in Power Query

Questa è la tipica disposizione delle cartelle e delle settimane disposte orizzontalmente e verticalmente. Da file a file, il numero di righe può variare a causa della variazione del numero di righe.

Ora vai su Power Query e ottieni i file. Seleziona Nuova fonte e fai clic su Altro. Quindi, fai clic su Tutto e scegli Cartella.

Selezionare Parametro e premere OK.

Ti mostrerà i file nella cartella. Devi riunire tutti i file in un'unica query di risultato. Facendo clic su Combina e trasforma si caricano i dati e si crea una serie di query.

Quindi, scegli qualsiasi file nella cartella come file di esempio e seleziona qualsiasi elemento che desideri avviare.

Guardando Query1, puoi vedere i dati sia da File1 che da File2.

Formulazione di calcoli nella Power Query

Il formato dei dati in Query1 è ancora errato. È possibile modificarlo tramite la query Trasforma file di esempio.

Se cambi il formato di quella query in una forma tabulare, servirà da modello per tutti i file nella cartella.

Rimuovi le prime 2 righe e Column1.

Quindi, riempi gli spazi vuoti all'interno del tavolo. Vai alla scheda Trasforma e seleziona Trasponi. Seleziona le colonne contenenti le date e fai clic sull'intestazione. Fai clic su Riempi e scegli Giù.

Le colonne con le date nella barra della formula indicano che se c'è un numero variabile di righe in ogni settimana, i risultati non saranno in quelle colonne. Quindi, elimina il passaggio Filled Down nella sezione Passi applicati.

Ogni gruppo di tabelle inizia con una colonna contenente date. Devi eseguire operazioni di base per riportarli in un formato tabulare.

Aggiungere un passaggio manuale premendo f(x) nella barra della formula. Quindi, inserisci Table.ToColumns . Questo trasforma ogni colonna in un elenco contenente elenchi.

Trasforma l'elenco in una tabella e aggiungi un numero di indice. Puoi cambiare da 0 o 1.

Colmare le lacune nelle date

Ci sono ancora lacune nelle date che devi colmare. Tuttavia, l'operazione di riempimento non funziona per gli elenchi. Devi trasformare questo elenco in una tabella.

Selezionare la colonna Indice e fare clic su Raggruppa per. Sostituisci Count in ToTable per modificare il nome della colonna. Modificare RowCount in FromColumns e puntare a Column1 dove vengono inseriti gli elenchi. Quindi, trasformalo in una tabella dei tipi.

Ora puoi vedere che l'elenco è trasformato in una tabella con una sola colonna.

Ora, esegui l'operazione di riempimento per ogni tabella. Innanzitutto, aggiungi una colonna personalizzata e chiamala FillDown .

Devi accedere al primo valore della prima colonna della tabella, quindi per la formula usa IF . Fare riferimento alla colonna ToTable che contiene la tabella. Inserisci Column1 come colonna a cui vuoi fare riferimento. Quindi, usa null tra di loro per recuperare il primo valore dall'elenco.

Quindi, controlla se si tratta di una data. Se lo è, riempi quella colonna con Table.Filldown. All'interno di quella funzione, inserisci ToTable e Column1. Ma se non è una data, inserisci ToTable per restituire l'intera tabella.

Questo ti darà una nuova colonna con le tabelle riempite.

Trova dove sono posizionate le date della colonna e suddividile in tabelle separate. Per fare ciò, devi trasformare le tabelle in un elenco di colonne.

Dopo le funzioni Table.FillDown e Else nella barra della formula, fai riferimento a Column1.

Questo trasformerà le tue tabelle in elenchi con un set di date.

Quindi, trasforma nuovamente l'elenco in una tabella aggiornando la colonna dell'indice. Ricorda che il punto di divisione è dove si trova la posizione della colonna della data. Quindi, aggiungi una colonna personalizzata e chiamala Index2 . Quindi, controlla se l'elenco contiene date.

Usa IF e inserisci FillDown. Devi solo controllare il primo valore nell'elenco. Se è una data, restituisce Index. In caso contrario, inserisci null . Quindi, riempi quella colonna.

Raggruppamento di tabelle separate

Utilizzare Raggruppa per nella colonna Indice2. In questo modo le tabelle con gli stessi valori Index2 vengono raggruppate in una sola.

Non conterai le file. Quindi, modifica RowCount in FromColumns e aggiungi la colonna FillDown. Quindi, modifica il tipo in tabella.

Se fai clic su una tabella, vedrai le colonne al suo interno.

Successivamente, devi aggiungere tutte le tabelle separate insieme in un'unica tabella. Crea un passaggio manuale e usa Table.Combine. Quindi, fai riferimento alla colonna Count al suo interno.

Selezionare Colonna1 e Colonna2. Fare clic su Trasforma e selezionare Unpivot altre colonne. Ti mostrerà quindi 4 colonne.

Rimuovi la colonna Attributo perché non è necessaria. Quindi, modifica i nomi delle colonne in Data , Movimenti e Nome . Modificare il tipo di dati della colonna Data in Data e la colonna Nome in Testo.

Ora hai aggiornato le trasformazioni al file di esempio di trasformazione.

Se vai a Query1, puoi vedere che Column1 della tabella non può essere trovata. Quindi, rimuovere il passaggio Tipo modificato nella sezione Passaggi applicati.

Infine, modifica i tipi di dati delle colonne. Impostare il tipo di dati della colonna Data su Data. Quindi, imposta il tipo di dati delle colonne Movimenti e Nome su Testo.

Ora hai aggiornato e modificato il formato del modello di dati in un formato tabulare.

Calendari personalizzati di LuckyTemplates: calcolo della modifica mese per mese - 445 calendari
Tecniche di formattazione condizionale personalizzata in LuckyTemplates
Come lavorare con più date in LuckyTemplates

Conclusione

Nel rapporto sui dati, devi colmare le lacune nella tabella delle date trasformando i layout del calendario in una forma tabulare.

È possibile trasformare più colonne utilizzando Power Query in LuckyTemplates. Ciò fornirà al tuo rapporto informazioni corrette e preziose da raccogliere.

Ti auguro il meglio,

Melissa