LuckyTemplates Power Query: rimozione di righe duplicate

In questo blog, ti mostrerò come rimuovere le righe duplicate nella LuckyTemplates Power Query in base alle condizioni. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Questo esempio è tratto da una domanda che è stata sollevata sul . Il membro dispone di dati transazionali e desidera rimuovere le righe che si annullano a vicenda.

Esistono due tipi di transazione distinti, uno per l'ingresso e uno per l'uscita. Ha anche aggiunto il conteggio dei movimenti. E sulla base di ciò, ha formulato tre scenari.

Lo scenario uno si basa su due righe. Se si annullano a vicenda, entrambe le righe devono essere rimosse. In caso contrario, è necessario mantenere entrambe le righe.

Gli scenari due e tre si basano su una combinazione di righe, che in parte si annullano a vicenda. Tali righe devono essere rimosse in base al criterio first-in-first-out in modo da conservare solo le transazioni in entrata più recenti.

Fondamentalmente, stiamo ripetendo la stessa logica più e più volte. Dovremmo essere in grado di progettare un unico approccio adatto a tutti questi scenari.

Alcune delle cose che faremo sono aggiungere una colonna con quantità assolute e raggruppare per "aggiungere una colonna saldo quantità" e una tabella nidificata utilizzando l'opzione "tutte le righe". Trasformeremo anche quelle tabelle nidificate, ordineremo il tipo di movimento e la data di registrazione, creeremo un totale parziale e manterremo le righe solo in base a una condizione.

L'ho valutato a un livello intermedio perché la maggior parte delle trasformazioni che possiamo fare qui è usando l'interfaccia utente. Detto questo, passiamo alla LuckyTemplates Power Query.

Sommario

• Aggiunta di colonne con quantità assolute nella query di alimentazione
• Raggruppa per indice ID e quantità assoluta
• Trasformazione delle tabelle nidificate in LuckyTemplates Power Query
  • Tipo di movimento di smistamento e data di registrazione
  • Creazione di un totale parziale
  • Filtrare le righe in base a una condizione
• Conclusione

Aggiunta di colonne con quantità assolute nella query di alimentazione

Inizieremo aggiungendo una colonna con quantità assolute. Seleziona la colonna Quantità , quindi sulla barra multifunzione o sulla scheda Aggiungi colonna , vai su Scientifico , quindi fai clic su Valore assoluto .

Rinominerò questa colonna nella barra della formula.

Raggruppa per indice ID e quantità assoluta

Ora possiamo raggruppare per indice ID e quantità assoluta. Selezionare ID Index , quindi tenere premuto MAIUSC o Ctrl per effettuare una selezione multipla, quindi fare clic su Abs qty (quantità assoluta).

Troverai Raggruppa per nella scheda Trasforma.

Ma puoi anche trovarlo nella scheda Home.

Fare clic su di esso e ora aggiungeremo una nuova colonna. Non è un "conteggio", ma sarà la "somma" della quantità. Quindi, rinominiamolo in Qty (quantity) Balance . Sarà una somma della colonna della quantità. E poi, aggiungeremo un'altra aggregazione, ma sarà speciale, quindi selezioneremo All Rows . Diamo anche un nome a questa colonna ( AllRows ). Quindi, premere OK.

Se clicchiamo nello spazio bianco nella tabella, vediamo un'anteprima della tabella nidificata in basso. Vediamo che le quantità per questa tabella nidificata si annullano a vicenda e il saldo della quantità è zero.

Se osserviamo la successiva tabella nidificata (quindi fai clic a lato nello spazio bianco della riga successiva), vediamo che la colonna della quantità non si annulla a vicenda e un saldo della quantità è maggiore di zero.

Aggiungiamo una colonna personalizzata in modo da poterla utilizzare successivamente per le nostre trasformazioni di tabelle nidificate. Quindi, seleziona Aggiungi colonna personalizzata .

Rinominiamo quella colonna in Trasformazioni e aggiungeremo zero (0) come segnaposto. Quindi, fare clic su OK.

Trasformazione delle tabelle nidificate in LuckyTemplates Power Query

Ora siamo pronti per creare la logica per trasformare quelle tabelle nidificate. Ma non vogliamo scriverne un mucchio e non ne abbiamo bisogno.

Possiamo utilizzare l'interfaccia utente per la maggior parte. Se costruiamo la logica per quelle trasformazioni in una query separata, lo scenario più elaborato che avevamo era quello con tre o quattro righe.

Ora, se clicco sul lato destro nell'ultima riga, vedo che questa tabella ha quattro righe. Quindi, possiamo usarlo per costruire le nostre trasformazioni.

Fare clic con il pulsante destro del mouse sul lato nello spazio bianco e selezionare Aggiungi come nuova query .

Vediamo che la tabella nidificata è ora espansa ed è stata aggiunta come nuova query e possiamo usarla per costruire la logica di cui abbiamo bisogno.

Tipo di movimento di smistamento e data di registrazione

Ora, la prima cosa che dobbiamo fare è ordinare in base al tipo di movimento . Scegliamo decrescente . In questo modo le transazioni in uscita saranno sempre in primo piano.

Successivamente, ordineremo la data di registrazione come ascendente , assicurandoci che se omettiamo righe, sarà sempre fatto secondo il principio FIFO (first in first out).

Creazione di un totale parziale

Per identificare le righe che rimuoveremo, aggiungerò un totale parziale da disabilitare e possiamo usare un List.FirstN per quell'attività. Questa funzione crea un elenco basato su un altro elenco, in cui gli elementi principali vengono mantenuti in base a un numero specifico oa un criterio.

Torniamo alla nostra domanda. Abbiamo bisogno di due cose per List.FirstN. Innanzitutto, abbiamo bisogno di quell'elenco e quell'elenco è la nostra colonna Quantità . Se faccio clic con il pulsante destro del mouse sull'intestazione della colonna Quantità, otteniamo l'opzione Aggiungi come NewQuery .

E qui vediamo il codice di cui abbiamo bisogno per generare quell'elenco. Nel passaggio applicato, puoi vedere che punta all'ultimo passaggio e quindi identifica la colonna in quelle parentesi.

La seconda parte di cui abbiamo bisogno per List.FirstN è un numero per identificare quanti numeri tenere da quell'elenco. Possiamo usare l'indice per questo, quindi aggiungerò una colonna indice da una (1) per questa riga.

Quindi, per il numero 1, manterrà il numero sulla prima riga nella colonna Quantità e così via. E ora possiamo riassumere.

Aggiungiamo una colonna personalizzata e chiamiamola Running Total . Usiamo List.FirstN qui, e possiamo vedere che il primo parametro era quell'elenco, la colonna Quantity. Dobbiamo puntare all'ultimo passaggio, e l'ultimo passaggio è l' Added Index .

Abbiamo identificato la colonna che vogliamo, e questa è la nostra colonna Quantità. Il nostro conteggio è nella colonna Indice.

Ora restituisce un elenco. Se clicchiamo a lato nello spazio bianco, possiamo vedere il contenuto di quella lista. Per il primo record, ha mantenuto solo la riga superiore di quell'elenco. Per il secondo record, ha mantenuto le prime due file.

Ora tutto ciò che dobbiamo fare è sommare queste quantità e possiamo usare Lists.Sum per farlo. Quindi, lo aggiungerò nella barra della formula. E inserirò anche Type .

Filtrare le righe in base a una condizione

Con questo totale parziale, possiamo identificare le righe che vogliamo conservare. Vogliamo mantenere solo le righe maggiori di zero, quindi aggiungiamo una condizione di filtro.

Ora possiamo rimuovere le nostre colonne di supporto. Selezionare Indice e totale parziale, quindi fare clic su Rimuovi colonne.

Nell'editor avanzato, ora possiamo copiare il codice che abbiamo creato. Aprirò l' editor avanzato e possiamo vedere che questo è il nostro passaggio diviso. Quindi, possiamo selezionare e copiare tutto ciò che c'è sotto.

Ora torniamo alla nostra query originale. Di nuovo, apri l'editor avanzato. E invece del segnaposto, spostiamoci su una nuova riga. Userò l'espressione " let " perché può acquisire valori da calcoli intermedi in variabili. Quindi, "lascia" e poi vai su una nuova riga e incolla le mie trasformazioni. Inoltre dichiarerò Type .

Il codice evidenziato sopra punta alla nostra tabella nidificata espansa, che si trova all'interno della colonna Tutte le righe. Quindi, lo indicherò, sostituendo questo codice evidenziato in AllRows .

Quindi la nostra colonna di trasformazione ora ha tabelle nidificate. Costruiamo la nostra query in base all'ultima riga, giusto? E questo conteneva quattro righe quando abbiamo iniziato e ne conteneva solo due. Abbiamo anche quelle quantità bilanciate a zero, giusto? E ora ha un tavolo vuoto.

Possiamo rimuoverli filtrando sulla colonna Saldo quantità. Non vogliamo che il saldo della quantità sia uguale a zero.

E poi, selezionerò la mia colonna di trasformazione e rimuoverò altre colonne. Ora posso espandere le mie tabelle nidificate di trasformazione. Non usare il nome della colonna originale come prefisso e premi ok.

Nella scheda Trasforma, seleziona Tipo di dati e abbiamo finito. Ed è così che ripulisci i dati transazionali.

Nozioni di base su unpivot e pivot in LuckyTemplates: revisione dell'editor di query
Suggerimenti pratici per l'utilizzo dell'editor avanzato di LuckyTemplates
Trasformazioni avanzate in LuckyTemplates

Conclusione

In questo tutorial, ti ho mostrato come ripulire i dati transazionali, in particolare rimuovendo le righe duplicate. Questa è un'ottima tecnica che puoi utilizzare quando utilizzi LuckyTemplates Power Query.

Spero ti sia piaciuto questo. Guarda il tutorial video completo qui sotto per maggiori dettagli. Controllare anche i collegamenti in basso per altri contenuti correlati all'editor di Power Query di LuckyTemplates.

Saluti!

Melissa