Annulla il pivot di più colonne in modo dinamico in LuckyTemplates

In questo tutorial, rivisiteremo un argomento trattato in precedenza su come annullare il pivot di più colonne in Power Query utilizzando List.Zip. Un membro di LuckyTemplates ha chiesto sul come rendere dinamico quel metodo. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

L'ho etichettato come avanzato perché scriveremo un certo numero di funzioni M e alcune di queste potrebbero anche essere nuove per te.

Detto questo, passiamo a Power Query.

Sommario

• Dati in formato tabellare
• Alterazione del codice per decomprimere dinamicamente più colonne
• Annullamento dinamico di più colonne in modo dinamico: aggiunta di una logica di colonna personalizzata
• Conclusione

Dati in formato tabellare

Come puoi vedere, questi dati sono già stati trasformati in un vero e proprio formato tablet adatto per l'analisi. Questo processo, sebbene molto importante, non sarà trattato qui, ma esamineremo brevemente ogni passaggio.

Nella fase di origine, vediamo una ripetizione costante delle coppie di valori, che sono le ore e i contatti per ciascuno degli attributi sopra elencati.

Nei passaggi applicati , puoi vedere il resto dei passaggi. Abbiamo rimosso alcune righe superiori, rimosso le colonne vuote, ripulito i nostri valori di testo e li abbiamo riempiti. Quindi, abbiamo trasposto i nostri dati, rimosso il record vuoto, unito le colonne uno e due, ripulito nuovamente i nostri valori di testo e trasposto nuovamente i nostri dati.

Abbiamo creato un passaggio personalizzato, che esamineremo più dettagliatamente in seguito, abbiamo estratto i valori dal nostro elenco e infine modificato il tipo.

Diamo un'occhiata all'intero codice. La cosa che ci interessa qui è la funzione List.Zip . Breve riepilogo, prende un singolo elenco come parametro e quindi restituisce un elenco. Di conseguenza, vediamo che questo singolo elenco in questo caso contiene tre elenchi separati.

Se esaminiamo il primo elenco, esso contiene nomi di attributi hardcoded, solo valori di testo che sono stati digitati. Il secondo elenco contiene riferimenti di colonna hardcoded per i valori dell'ora. L'elenco finale contiene i riferimenti di colonna hardcoded per i valori di contatto.

List.Zip quindi prende un elemento da ciascuno di questi tre elenchi e li combina in base alla loro posizione in un nuovo elenco. Ad esempio, ogni primo elemento di tali elenchi viene combinato in un nuovo elenco. Lo stesso accade per tutti i valori elencati per secondi in quell'elenco e così via.

I valori hardcoded possono essere interessati se è improbabile che gli attributi cambino nel tempo. Quindi, se la prossima volta non sono presenti tutti gli elementi o ne esistono di nuovi, potresti metterti nei guai.

Alterazione del codice per decomprimere dinamicamente più colonne

Quindi, come rendiamo questa dinamica?

La prima cosa che faccio è scorrere il codice per vedere se c'è un posto dove possiamo raccogliere quei valori di attributo senza dover progettare un passaggio separato per farlo. Quindi faremo un passo indietro nel codice e questo passaggio RemoveNull è perfetto.

Puoi vedere che elenca tutti i valori degli attributi qui nella prima colonna. Possiamo usare l'interfaccia utente per estrarli.

Quindi fai clic con il pulsante destro del mouse sull'intestazione e in basso seleziona Aggiungi come nuova query .

Otteniamo questo elenco e questa è una buona notizia perché vogliamo sostituire l'elenco codificato che abbiamo visto prima con quello dinamico. Tutto quello che dobbiamo fare è riordinare i valori.

Iniziamo rimuovendo quei null. Quindi, all'interno della barra della formula, aggiungeremo un List.RemoveNulls . Come puoi vedere ora, tutti gli elementi vengono ripetuti e questo a causa delle coppie di valori. È elencato una volta per le ore.

E poi di nuovo, per i contatti, per rimuovere quei duplicati, possiamo aggiungere un List.Distinct . Ora abbiamo creato questa logica e tutto ciò che dobbiamo fare è copiarla e incollarla nella nostra query di esempio.

Nella query di esempio aprire l'editor avanzato e creare un nuovo nome di variabile. Chiamiamolo, AttributeList . Quindi incolliamo il codice e mettiamo una virgola.

Ora torniamo al nostro passaggio personalizzato. All'interno di List.Zip, possiamo sostituire il primo elenco con il nome della nostra variabile (AttributeList).

Ora diamo un'occhiata più da vicino al secondo elenco. Sappiamo che fa riferimento ai nomi delle colonne e ognuno di quei nomi di colonna inizia con lo stesso attributo che abbiamo già in quell'elenco seguito da uno spazio e da un testo "ore".

Abbiamo solo bisogno di aggiungere un suffisso a ciascun elemento. Torniamo quindi indietro di un passaggio e aggiungiamo una colonna personalizzata per creare un po' di logica.

Annullamento dinamico di più colonne in modo dinamico: aggiunta di una logica di colonna personalizzata

È solo una colonna temporanea, quindi non è necessario assegnarle un nome appropriato. Lo rimuoveremo quando avremo finito.

Per modificare i valori in un elenco, possiamo utilizzare la funzione List.Transform . Dobbiamo trasformare i valori nel nostro AttributeList , quindi lo incollerò.

Qui nelle intestazioni, vediamo che i valori del testo iniziano con una lettera maiuscola, e poi il resto è minuscolo. Il nostro attributo contiene solo una parola in maiuscolo, quindi dobbiamo trasformare anche quel valore di testo. E possiamo usare il trattino basso per accedere a ciascun elemento dell'elenco e aggiungeremo quel suffisso.

Con ciò, otteniamo un valore di elenco nella nostra colonna. Non importa quale selezioni, ma fai semplicemente clic di lato nello spazio bianco in uno qualsiasi di essi qui.

Sotto la pagina, vediamo un'anteprima dell'elenco che abbiamo creato. Quindi vedi che ora le intestazioni di colonna sono correttamente rivestite in modo che corrispondano alle intestazioni di colonna che vediamo qui con questo elenco.

Ora abbiamo i mezzi per identificare i campi del nostro record a cui vogliamo accedere. Quindi apriamo di nuovo la finestra di dialogo della colonna personalizzata e aggiungiamo Record.SelectFields . Vuole un record come record, quindi possiamo usare quel carattere di sottolineatura per accedere al record corrente in cui ci troviamo. E poi gli abbiamo dato un elenco con tutti quei nomi di campo, quindi aggiungi una parentesi di chiusura e premi OK .

Quindi non abbiamo più un elenco, ma ora otteniamo un record. Facciamo di nuovo clic di lato nello spazio bianco per visualizzare il contenuto di quel record. Qui vediamo che elenca i nomi dei campi dei record ei valori dei campi dei record.

Ma siamo interessati solo ai valori del campo record, quindi dobbiamo estrarli. Torniamo alla nostra finestra di dialogo della colonna personalizzata e aggiungiamo Record.FieldsValues ​​.

Ora otteniamo di nuovo un elenco e non più un record. Facciamo di nuovo clic di lato nello spazio bianco e vedrai che ora abbiamo un elenco con solo quei valori. Quindi sembra buono.

Copiamo l'intera logica che abbiamo creato, ad eccezione dell'ultima parentesi di chiusura. E ora possiamo rimuovere la nostra colonna temporanea perché non ne abbiamo più bisogno.

Sulla nostra barra della formula, selezioniamo il nostro secondo elenco e lo sostituiamo con la nostra logica.

Per creare un elenco con i valori dei campi record per i contatti, tutto ciò che dobbiamo modificare è il suffisso. Quindi possiamo selezionare anche l'ultimo elenco e incollare la nostra logica.

Quindi, invece di ore , cambiamo questo in contatti .

Nessun errore e sembra buono. Questa è ora una soluzione dinamica per annullare il pivot di più colonne.

Colonne unpivot di LuckyTemplates - Esercitazione su Power Query
Come eseguire il unpivot delle colonne in LuckyTemplates
Nozioni di base su unpivot e pivot in LuckyTemplates - Revisione dell'editor di query

Conclusione

È così che si annulla dinamicamente più colonne in utilizzando List.Zip.

Se arriva un nuovo attributo, verrà automaticamente raccolto da AttributeList. E quando un elemento non esiste più, non sarà presente in quella lista.

Spero ti sia piaciuto questo. Guarda il video completo di seguito per ulteriori dettagli su questo tutorial e dai un'occhiata ai collegamenti seguenti per altri contenuti sull'unpivot di più colonne in LuckyTemplates.

Ti auguro il meglio

Melissa