Offset nel Parsing Log

[realkaranka]

Ciao a tutti,

innanzitutto ringrazio chiunque avrà la pazienza di seguirmi con il mio problema. Da poco tempo sono entrato nel mondo Power Query e ho cominciato con elaborazioni semplici senza grossi problemi, sorpreso dalla potenza di calcolo.
Il mio obiettivo adesso è eseguire il parsing di alcuni log (cosa che ho già cominciato a fare) e successiva elaborazione degli stessi.
Sono arrivato al punto che riporto nell'esempio in allegato. Devo passare dalla tabella in ingresso alla tabella desiderata. La logica che devo adottare è la seguente: se le /api/A, /api/B, /api/C, /api/D hanno una request_time inferiore a 3 secondi le devi accorpare come una unica. All'interno di un log la sequenza di queste 4 api si può replicare N volte, ma quelle da accorpare sono quelle che si trovano all'interno di un certo offset che io ho tarato a 3 secondi. Dopo l'accorpamento, le colonne Info_1 e Info_2 devono contenere i valori della /api/B.
Secondo voi tutto questo è possibile in DAX?
grazie mille
Giacomo

[Andrea90]

@realkaranka,

Non mi è chiaro perché la riga 8 relativa alla "/api/D" con un tempo "17/05/2023 08:32:32" non compare nella tabella di output.

Andrea

[realkaranka]

Si scusami nella colonna A api/D doveva avere un timestamp di 8:28 per rientrare nel calcolo. Appena posso correggo file in allegato

Scusatemi ancora
Giacomo

[Andrea90]

@realkaranka,

Ti spiego la logica che avrei pensato.
Visto che ti serve fare questo accorpamento per le api di tipo A/B/C/D allora prenderei la tabella iniziale e la filtrerei per solo queste tipologie (le altre le terrei da parte al momento).

Metti poi in ordine la colonna con le date e le ore, da qui estrapoli in una nuova colonna solo il valore Orario.
Aggiungi una colonna indice come numero progressivo e poi ne aggiungi un'altra con il valore dell'indice corrente -1

Fai un merge tra questa tabella e la stessa basandosi sui due valori di indice che hai calcolato prima. Ad es° se il valore indice della riga corrente è 3 avrai il valore 2 come indice della riga precedente da riportare.

Codice: Seleziona tutto

= Table.NestedJoin(#"Inserted Subtraction", {"Pre Idx"}, #"Inserted Subtraction", {"Index"}, "Tabella1 (2)", JoinKind.LeftOuter)

In questo modo, su ogni riga avrai il valore Tempo Corrente e valore Tempo Precedente, con l'unica eccezione della prima riga che sarà null.

A questo punto farai una formula che calcoli il delta tra i due tempi e andrai a valutare se in termini di minuti questo delta supera la soglia desiderata (3 minuti nel tuo caso). Se la supera riporti il valore Ora della riga corrente altrimenti null.

Codice: Seleziona tutto

= Table.AddColumn(#"Expanded Tabella1 (2)", "Test", each if [Pre Time] = null then [Time] else if Duration.Minutes([Time] - [Pre Time]) > 3 then [Time] else null, type time)

L'unica eccezione sarà la prima riga che conterrà per forza il valore Tempo originale visto che non ha nessun dato precedente.

Otterrai qualcosa di simile:


A questo punto, se la logica che ti ho indicato segue quella da te desiderata, il gioco è semplice potrai crearti i raggruppamenti che vorrai perché anche solo banalmente con un riempimento verso il basso potrai avere lo stesso orario di riferimento per tutte le righe che soddisfano la tua logica.

p.s. ho lasciato il file come lo hai inviato, per questo vedi anche quella riga in più per l'api D...

Andrea

[realkaranka]

Ciao Andrea,

grazie del tuo tempo.
Ho provato a fare quello che hai detto ma mi sono perso verso la fine. Ti riallego excel con query.

grazie
Giacomo

[Andrea90]

@realkaranka,

Quando alleghi un file contenente una query che prende i dati da un excel esterno devi allegare anche la sorgente dati, altrimenti non riusciamo a lavorarci. Comunque ho preso come origine la tabella di esempio eliminando poi i passaggi che non servivano.

Quell'operazione che hai eseguito di separare la data dall'ora tramite delimitatore è sbagliata. Visto che il campo "Data Request" è di tipo datetime puoi selezionare il menu in alto (tenendo selezionato il campo):
Add Column --> Time --> Time Only

L'indice fallo partire da 1 giusto per non dover lavorare con dei negativi (quando farai 0 - 1 = -1)
Non cambia nulla a livello di calcolo, ma forse è più "bello" lato estetico.

Il secondo [Indice] è semplicemente un [Indice] -1 non c'è bisogno di utilizzare di nuovo Table.AddIndexColumn()

Il merge non hai bisogno di lavorare su un duplicato della tabella. Il codice che hai creato tramite GUI:

Codice: Seleziona tutto

= Table.NestedJoin(#"Indice aggiunto 1", {"Indice"}, #"Foglio2 (2)", {"Indice (2)"}, "Foglio2 (2)", JoinKind.LeftOuter)

Inoltre dalla prima tabella devi prendere il valore dell'indice -1 non quello corrente, altrimenti ad ogni riga lui ti lega il dato del tempo della riga che viene dopo.

Il codice quindi lo sostituisci con:

Codice: Seleziona tutto

= Table.NestedJoin(#"Indice aggiunto 1", {"PreIDX"}, #"Indice aggiunto 1", {"Indice"}, "Foglio2 (2)", JoinKind.LeftOuter)

Nell'ultimo passaggio hai preso e copiato la mia formula dopo che hai cliccato su aggiungi colonna custom. Il codice che ti avevo dato io era già completo, così come hai scritto invece ti genera per ciascuna cella un oggetto tabella.... devi solo premere sul pulsante fx che compare nella barra delle formule e sostituire quello che viene fuori con il codice che ti ho indicato (eventualmente cambiando le intestazioni se sono diverse dalle tue).

Andrea

[realkaranka]

Esempio_parsing.xlsx

(24.92 KiB) Scaricato 31 volte

@Andrea90

sono riuscito a fare quello che mi hai consigliato e adesso per l'esempio che ti ho condiviso riesco a fare quello che volevo. Ho anche inserito le Info_1 e Info_2 della riga che mi serve.
Il problema è che questo funziona con riga presente e precedente ma a me serve questa logica solo quando si presentano tutte e quattro le API (A,B,C,D). Se se ne presenta solo una o se sono spalmate con Time superiore a 3 sec io non la devo filtrare.
Quindi ad esempio :

/api/A 17/05/23 08:26
/api/B 17/05/23 08:26
/api/C 17/05/23 08:26
/api/D 17/05/23 08:26
/api/B 17/05/23 08:27
/api/C 17/05/23 08:27
/api/D 18/05/23 18:22

Vorrei che diventasse:
/api/A 17/05/23 08:26 (le prime quattro si accorpano in una)
/api/B 17/05/23 08:27 (rimane perché non c'è la quartina)
/api/C 17/05/23 08:27 (rimane perché non c'è la quartina)
/api/D 18/05/23 18:22 (rimane perché non c'è la quartina)

Inoltre come faccio a recuperare /api/X, /api/Y, /api/Z che abbiamo filtrato all'inizio?

Allego file fino a dove sono arrivato (non ho sorgenti esterne)

grazie ancora
Giacomo

[Andrea90]

Inoltre come faccio a recuperare /api/X, /api/Y, /api/Z che abbiamo filtrato all'inizio?

Questa cosa è banale, visto che su queste righe non devi eseguire nessun calcolo farai un duplicato della tua tabella di partenza, in una filtri per i codici a,b,c,d ed esegui le logiche che ti servono.

Nell'altra non dovrai far nulla e l'accoderai alla prima una volta che avrai finito i tuoi calcoli.

solo quando si presentano tutte e quattro le API (A,B,C,D). Se se ne presenta solo una o se sono spalmate con Time superiore a 3

La logica applicata, dovrebbe mostrarti una cosa simile:
Iniziale------------------------Finale
/api/D 18/05/23 18:22 /api/D 18/05/23 18:22
/api/A 17/05/23 08:26 /api/A 17/05/23 08:26
/api/B 17/05/23 08:26
/api/C 17/05/23 08:26
/api/D 17/05/23 08:26
/api/B 17/05/23 08:27 /api/B 17/05/23 08:27
/api/C 17/05/23 08:27

questo vuol dire che se trascini in basso avrai una cosa simile:
Iniziale------------------------Finale
/api/D 18/05/23 18:22 /api/D 18/05/23 18:22
/api/A 17/05/23 08:26 /api/A 17/05/23 08:26
/api/B 17/05/23 08:26 /api/A 17/05/23 08:26
/api/C 17/05/23 08:26 /api/A 17/05/23 08:26
/api/D 17/05/23 08:26 /api/A 17/05/23 08:26
/api/B 17/05/23 08:27 /api/B 17/05/23 08:27
/api/C 17/05/23 08:27 /api/B 17/05/23 08:27

Se tu raggruppi per la colonna Finale ci saranno alcune combinazioni che conterranno quattro righe, altre che ne conterranno un numero inferiore. Quelle con un conteggio di quattro righe le lasci accorpate, le altre le espanderai nuovamente lasciandole come voci distinte.

Andrea

[realkaranka]

Ottime intuizioni... Provo ad applicare il tutto sul mio master file e ti faccio sapere.
Grazie per la disponibilità

Giacomo

[realkaranka]

Ciao,

sto continuano con il mio parsing e mi sono bloccato in un altro punto.

Devo trovare un modo per filtrare le api con valori non nulli e non univoche in questo modo, solo la prima in ordine temporale deve avere indice 1 le altre che si ripetono no:

API_NAME VALUE INDEX
api/A 1111 1
api/A 2222 1
api/A 3333 1
api/B null
api/A 1111
api/A 2222
api/A 3333
api/B null
api/A 1111
api/A 2222
api/A 3333
api/B null
api/B null
api/A 4444 1
api/A 5555 1
api/A 6666 1

Ho provato con raggruppa ma riesco a raggiungere il mio obiettivo.

grazie mille
Giacomo