Power Query M: modello 0-1 alternato nella tabella delle date

Questa esercitazione è incentrata sull'uso della funzione numero.mod M di Power Query per creare un modello 0-1 alternato nella tabella Data. La soluzione che ti mostrerò è arrivata da uno degli utenti esperti della nostra community, Rajesh, di LuckyTemplates. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Lo scopo è quello di generare una sequenza di quattro zeri alternati a quattro uno, per poi ripeterla nell'arco della giornata.

Rajesh è stato perfetto con la sua analisi e l'analisi del problema utilizzando la funzione Power Query M per risolverlo. Detto questo, passiamo a Power Query.

Sommario

• La funzione Power Query M Number.Mod
• Creazione di una colonna personalizzata per il modello Power Query M
• Creazione di un costrutto IF-THEN-ELSE
• Conclusione

La funzione Power Query M Number.Mod

Power Query M, in particolare la funzione number.mod, sarà il fulcro della nostra soluzione. Che cosa fa?

Puoi controllare la documentazione interna di qualsiasi funzione M inserendo il suo nome senza parentesi come mostrato qui nella barra della formula. Questa funzione restituisce il resto di una divisione intera. Occorrono fino a tre parametri, i primi due, un numero, e il divisore, anch'esso un numero.

Ora cominciamo. Selezionerò la mia query di esempio che contiene un intervallo di date che ho preparato.

In molti casi, number.mod viene utilizzato in combinazione con una colonna dell'indice che viene quindi utilizzata come primo parametro. Ma non lo farò perché se ci pensi, il valore della data è un numero intero. Pertanto, creare un indice che condivida la stessa granularità della colonna Date, in cui ogni riga ha un valore univoco, è qualcosa che possiamo evitare.

Tutto quello che dobbiamo fare per imitare un indice è sottrarre la primissima data nella nostra tabella Date da ogni data nella colonna Date. Per fare ciò, possiamo memorizzare il valore della data di inizio in una variabile in modo da poterlo utilizzare più e più volte. Trovo che l'editor avanzato sia il posto migliore per farlo.

Quindi aprirò la finestra dell'editor avanzato e creerò una nuova variabile. Chiamiamolo StartDate . Ho codificato il mio primo appuntamento qui, quindi lo copio e incollo. Non dimenticare la virgola alla fine e ora possiamo sostituire quel primo appuntamento con la nostra variabile Data di inizio.

Come puoi vedere, non è cambiato nulla.

Creazione di una colonna personalizzata per il modello Power Query M

Quindi tramite questa mini tabella, aggiungeremo una colonna personalizzata.

Chiamiamo questa colonna, Pattern , e immettiamo la funzione Power Query M, number.mod . Qui puoi vedere i due parametri, un numero come numero nullable e un divisore come numero nullable. Quindi richiede due valori di tipo numerico come parametri.

Ora non abbiamo numeri, giusto? Abbiamo date e non esiste una conversione automatica del tipo in esse. Quindi dobbiamo ottenere il numero dal nostro valore di data. Quindi digitiamo qui, number.from quindi la nostra colonna Date e quindi sottraiamo la nostra variabile StartDate.

Ora, supponiamo che la prima data nella nostra tabella Date restituisca uno, quindi anche il nostro StartDate restituirà uno. E quindi, uno meno uno fa zero. Voglio che la nostra numerazione dell'indice inizi con uno invece che con uno zero. Quindi dovremo aggiungere uno indietro più uno. Ora uno meno uno uguale a zero più uno restituisce uno per il primo record.

Per il secondo record, otteniamo un valore due (2) per la data nella colonna Data, meno uno (1) dal nostro valore StartDate che è uguale a uno, più quello che stiamo aggiungendo indietro restituisce due e così via . Il nostro modello ripetuto era basato su una serie di quattro valori alternati. Quindi, come divisore, passerò il valore quattro (4).

Vediamo cosa fa. Nella riga quattro otteniamo zero, così come nella riga otto. Quindi ogni quarta occorrenza restituisce un valore zero per quel record.

Creazione di un costrutto IF-THEN-ELSE

Torniamo alla nostra finestra di dialogo con il cliente ed estendiamo la logica che abbiamo scritto finora. Fatta eccezione per il divisore, dobbiamo ripetere la stessa logica per poter identificare un'ottava occorrenza.

Tuttavia, su quell'ottava riga, vogliamo un valore diverso. Quindi, per fare una distinzione tra una quarta e un'ottava occorrenza, possiamo usare un costrutto IF-THEN-ELSE. In questo modo, possiamo restituire un valore per il primo test logico che restituisce true.

Quindi If , e incollerò qui il nostro codice (ad eccezione del divisore), cambia quel divisore in un otto in modo che un'ottava riga restituisca zero. Quindi, se è uguale a zero, significa che fallirà la tua strada verso vero o falso. Quindi vogliamo uno (1). Altrimenti , se la nostra logica qui identifica una quarta riga uguale a zero, allora vogliamo zero (0). Altrimenti non vogliamo recuperare un valore. Quindi aggiungiamo un null .

Ora, controlliamo di nuovo la nostra logica. Quindi ad ogni quarta occorrenza, otteniamo quello zero. E poi, l'ottavo, ne prendiamo uno. Successivamente, otteniamo zero e uno, e così via.

Quindi questo sembra buono. Tutto quello che dobbiamo fare ora è riempire questi valori.

Quindi, all'interno della barra della formula, aggiungeremo un Table.FillUp all'inizio, quindi alcune parentesi di apertura. Alla fine del nostro codice, aggiungiamo il numero del tipo : dobbiamo anche aggiungere una colonna come elenco. Quindi, andiamo comma , quindi usiamo le parentesi graffe per l'inizializzatore di elenco e come valore di testo passiamo nel nome della nostra colonna, Pattern . Non dimenticare le parentesi di chiusura e premi OK.

Guida per principianti al codice M in LuckyTemplates
Funzione M per la tabella delle date: come aggiungere un parametro nell'editor di query
Ordinamento delle colonne della tabella delle date in LuckyTemplates

Conclusione

Questo è uno scenario davvero unico e, onestamente, non riesco a pensare a un'applicazione per questo modello specifico, ma si spera che tu possa vedere il potenziale della creazione di sequenze utilizzando la funzione Power Query M, number.mod.

Per maggiori dettagli, guarda il video completo qui sotto per questo tutorial.

Ti auguro il meglio!

Melissa