Funzioni di query personalizzate di LuckyTemplates

Per l'esercitazione di oggi, creeremo una funzione di query personalizzata di LuckyTemplates che gestirà un formato comune di dati in pila difficile da gestire. Questa tecnica è facilmente riutilizzabile su più report e più tabelle. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Questa è una continuazione del tutorial che ho fatto di recente in cui abbiamo affrontato un formato comune di dati in pila difficile da gestire e utilizzato Modulo, Pivot e alcune funzioni di pulizia con Power Query per trasformare quei dati in un formato organizzato e funzionale.

È molto probabile che dovrai ripetere questo processo. Se questo tipo di formato è il risultato di un copia e incolla, probabilmente accadrà di nuovo. Quindi, in questo caso, probabilmente vorrai creare una funzione personalizzata che sarà riutilizzabile su più report e più tabelle, indipendentemente da come sono nominati e da cos'altro c'è in quelle tabelle.

Sommario

• Come creare ed eseguire il debug delle funzioni di query personalizzate di LuckyTemplates
• Conclusione

Come creare ed eseguire il debug delle funzioni di query personalizzate di LuckyTemplates

Passiamo a Power Query su questo. Se andiamo alla nostra tabella dei dati, nell'Editor avanzato, abbiamo il codice M per come siamo finiti per passare dai dati dello stack al formato desiderato.

Se vogliamo riutilizzarlo, facciamo semplicemente clic con il pulsante destro del mouse su Dati e diciamo Crea funzione .

Dice che non farà riferimento a nessun parametro, ma va bene creare una funzione senza parametri. Risponderemo, Sì, perché aggiungeremo i parametri man mano che andiamo nell'Editor avanzato. Chiameremo questa funzione, Unstack – 3 Elements , solo perché abbiamo le tre righe nel formato raw. Se avessimo un formato a due righe, probabilmente ne creeremmo uno separato composto da due o quattro elementi.

Quindi ora abbiamo questo Invoke, non abbiamo parametri e andiamo all'editor avanzato e iniziamo a lavorare su questo.

Primo, non abbiamo bisogno di questa Fonte. Useremo i parametri della funzione per definire la nostra fonte.

Vogliamo definire il parametro che entrerà. Lo facciamo con una parentesi aperta e poi un nome di parametro. Chiamiamo questo, Stack , che sarà quella colonna di dati che sono i dati dello stack non formattati che provengono dall'incollaggio degli indirizzi email.

Vogliamo che questo entri come un elenco e vogliamo che questo risulti in una tabella perché mentre lo scorporiamo da quella singola colonna a più colonne, passa da un elenco a una tabella. E ora, dobbiamo convertire l'elenco in entrata in una tabella in modo da poter aggiungere il nostro indice, avviare il nostro Modulo unpivot e fare tutto ciò che dobbiamo fare per riformattarlo.

Lo chiameremo ConvertStack e utilizzerà una funzione chiamata Table.FromList . Come molte delle funzioni M, fa esattamente quello che dice. Ci vuole solo un elenco. In questo caso, l'elenco sarà il nostro elenco Stack. E poi, dobbiamo sostituire Source qui con il nostro ConvertStack. Sembra abbastanza buono! Non abbiamo errori di sintassi, quindi premiamo Fatto.

Ora ci sta dando quello che volevamo, ovvero la possibilità di scegliere una colonna.

Quindi, sceglieremo TestData, ovvero i dati formattati in modo errato. Sceglieremo la colonna Valore, quindi faremo clic su OK. Successivamente, facciamo clic su Richiama.

Tuttavia, otteniamo questo errore. È davvero difficile individuare quale sia questo errore. Uno dei problemi con le funzioni personalizzate è che non interrompono i passaggi applicati, ottieni solo un passaggio per l'intera funzione personalizzata. Ma c'è un modo per aggirare questo problema nel debugging che ti mostrerò. Sarà molto più facile capire cosa sta succedendo qui.

Invece di eseguirlo per il momento come funzione, lo eseguiremo come query. Inizieremo manualmente quella chiamata Stack. Dobbiamo definire cos'è Stack perché non lo stiamo disegnando attraverso l'interazione di sceglierlo in Invoke. Quindi, abbiamo Stack uguale a TestData e la colonna Value di TestData.

Quello che fa è suddividerlo ora nei passaggi applicati. Questo ci aiuterà a capire perché non funziona. Nota una cosa interessante quando inseriamo Stack. Inizialmente, inserisce TestData[Value], ma quando lo convertiamo in una tabella, invece di Value è l'intestazione di colonna, l'intestazione di colonna è ora Column1.

Ricorda il messaggio di errore, era che non riusciva a trovare la colonna Valore. Il motivo per cui non è stato possibile trovare la colonna Value è che la funzione Table.FromList la rinomina in Column1.

Se scendiamo nei passaggi applicati, puoi vedere che è nel passaggio Personalizzato aggiunto dove otteniamo l'errore. Qui è dove non è stato trovato il valore del record.

Quindi, se andiamo nell'Editor avanzato, troviamo quel campo Personalizzato e possiamo vedere che si trova nel Text.Remove dove stiamo eliminando quei caratteri spazzatura di cui non avevamo bisogno, ma a cui si riferisce ancora il campo Valore. Lo cambieremo in Column1. Allo stesso modo, nel campo Colonne rimosse, fa riferimento a Valore, quindi lo cambieremo anche in Colonna1.

E quando arriviamo alla fine dei Passi Applicati, produce il risultato finale perfetto.

Ora che sappiamo che sta funzionando bene, abbiamo solo un'altra cosa da fare. Ricorda che per eseguire il debug, abbiamo trasformato la funzione personalizzata in una query. E così, ora dobbiamo prendere quella query e trasformarla di nuovo in una funzione.

Creazione di una funzione di pulizia del testo personalizzata in Power Query
Codice M in colonne personalizzate Power Query | Query e operatori della funzione LuckyTemplates
nell'editor di query

Conclusione

In questo tutorial, abbiamo creato questa funzione personalizzata, ne abbiamo eseguito il debug, l'abbiamo testata e funziona alla grande. E così ora, ogni volta che abbiamo uno stack di tre elementi, possiamo semplicemente selezionare quella tabella, scegliere quella colonna all'interno della tabella ed eseguire quella funzione personalizzata. Quindi, applica il Modulo, l'unpivot e ripulisci.

Questo dovrebbe darti degli ottimi strumenti in termini di creazione di funzioni personalizzate. Inoltre, ricorda quel trucco sul debug trasformandolo di nuovo in una query da una funzione e quindi ripristinandolo dopo il debug. È uno strumento davvero prezioso che semplifica notevolmente il debug delle funzioni personalizzate.

Ti auguro il meglio!