INSERIRE DIFFERENZA TRA DUE RIGHE IN POWER QUERY

Non è propriamente un programma di BI, ma nelle sue versioni più recenti anche Excel può fare grandi cose!

Moderatore: Utilizzo_prof_Excel

Rispondi
Avatar utente

Alieno86
Messaggi: 47 | Topic creati
Iscritto il: mer 9 giu 2021, 16:36
Ringraziato: 5 volte

INSERIRE DIFFERENZA TRA DUE RIGHE IN POWER QUERY

Messaggio da Alieno86 »

Buongiorno,
scrivo per una piccola problematica.

Avrei la necessità di fare la differenza tra i valori contenuti in record distinti con descrizione e codice differente inoltre l'importo è su due colonne differenti.
Scusate la scarsa capacità d'esprimermi, ho allegato un file con un paio di righe che dovrebbe premettere di capire con più semplicità.
Ho evidenziato i campi che possono essere utili in blu e in giallo i campi che "dovrebbero" essere creati.

Non mi è comoda una misura in Dax da abbinare al database, preferirei se fattibile andare a modificare il modello dati tramite un passaggio in power query.

Grazie per la disponibilità anche solo a leggere questo post.
Allegati
x forum mondo bi.xlsx
(15.66 KiB) Scaricato 50 volte


Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

INSERIRE DIFFERENZA TRA DUE RIGHE IN POWER QUERY

Messaggio da Andrea90 »

Ciao Alieno86,

Hai provato con un merge di query utilizzando come chiave il campo Matricola?

Se ti serve solo la voce 5038 puoi fare un riferimento alla query originale, eliminare tutte le righe che non contengono 5038 e poi fare il merge.

Avrai quel 99,72 su di una nuova colonna.

Se invece vuoi creare una nuova riga allora mi viene da dire che devi passare per un accodamento dopo l’operazione di merge tra due query di appoggio … una che contiene solo le voci 5004 e una che contiene le voci 5038.

A presto,
Andrea
Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link
Avatar utente

Autore del topic
Alieno86
Messaggi: 47 | Topic creati
Iscritto il: mer 9 giu 2021, 16:36
Ringraziato: 5 volte

INSERIRE DIFFERENZA TRA DUE RIGHE IN POWER QUERY

Messaggio da Alieno86 »

grazie mille,
purtroppo il database è abbastanza grosso circa 7milioni di record al momento, io pensavo, ma probabilmente non si puo fare di:
1.inserire un passaggio di filtro e ritrovarmi descrizioni DIMINUZIONE EX-INADEL TFR e I.N.A.D.E.L T.F.R.
2.poi unire le righe a coppie facendo la differenza su importo ente (formula funzione m che non conosco)
3.rimuovere il filtro.

Per le mie esigenze basterebbe che potessi passare il valore della voce DIMINUZIONE EX-INADEL TFR sul campo IMPORTENTE con un segno negativo.

in alternativa mi pare di capire potrei risolvere triplicando il modello dati:
modello dati completo
modello dati con solo voce DIMINUZIONE EX-INADEL TFR
modello dati con solo voce I.N.A.D.E.L T.F.R.
fare un merge tra i due a differenza...
accodare il tutto al modello principale
rimuovere le righe contenenti voci DIMINUZIONE EX-INADEL TFR e I.N.A.D.E.L T.F.R.
mi permetto di allegare il codice M relativo alla creazione del modello dati non credo sia utile ma magari all'occhio esperto si notano macroscopiche castronerie.

Grazie ancora sempre gentilissimo.
Francesco

Codice: Seleziona tutto

let
    Origine = Sql.Database("PERSO0XXX-89915\SQLEXPRESS", "AOB"),
    dbo_2015 = Origine{[Schema="dbo",Item="2015"]}[Data],
   
    Origine2 = Sql.Database("PERSO0XXX-89915\SQLEXPRESS", "AOB"),
    dbo_2016 = Origine2{[Schema="dbo",Item="2016"]}[Data],
  
    Origine3 = Sql.Database("PERSO0XXX-89915\SQLEXPRESS", "AOB"),
    dbo_2017 = Origine2{[Schema="dbo",Item="2017"]}[Data],

    Origine4 = Sql.Database("PERSO0XXX-89915\SQLEXPRESS", "AOB"),
    dbo_2018 = Origine4{[Schema="dbo",Item="2018"]}[Data],

    Origine5 = Sql.Database("PERSO0XXX-89915\SQLEXPRESS", "AOB"),
    dbo_2019 = Origine5{[Schema="dbo",Item="2019"]}[Data],

    Origine6 = Sql.Database("PERSO0XXX-89915\SQLEXPRESS", "AOB"),
    dbo_2020 = Origine2{[Schema="dbo",Item="2020"]}[Data],

    Origine7 = Sql.Database("PERSO0XXX-89915\SQLEXPRESS", "AOB"),
    dbo_2021 = Origine2{[Schema="dbo",Item="2021"]}[Data],


    #"UNIONE DELLE TABELLE"= Table.Combine({#"dbo_2015", #"dbo_2016", #"dbo_2017", #"dbo_2018", #"dbo_2019", #"dbo_2020", #"dbo_2021"}),
    #"Rimosse colonne" = Table.RemoveColumns(#"UNIONE DELLE TABELLE",{"ID"}),

    #"Duplicata colonna" = Table.DuplicateColumn(#"Rimosse colonne", "MESE_CEDOLINO", "MESE_CEDOLINO - Copia"),
    #"Duplicata colonna1" = Table.DuplicateColumn(#"Duplicata colonna", "ANNO_CEDOLINO", "ANNO_CEDOLINO - Copia"),
    #"Merge di colonne" = Table.CombineColumns(#"Duplicata colonna1",{"ANNO_CEDOLINO", "MESE_CEDOLINO"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"DATA_CASSA"),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Merge di colonne",{{"DATA_CASSA", type date}, {"MATRICOLA", Int64.Type}, {"DATA_NASCITA", type date}, {"DATA_ASSUNZIONE", type datetime}, {"DATA_ANZIANITA", type date}, {"COD_VOCE", Int64.Type}, {"ANNO_RIFERIM", Int64.Type}}),
    #"Duplicata colonna2" = Table.DuplicateColumn(#"Modificato tipo", "ANNO_RIFERIM", "ANNO_RIFERIM - Copia"),
    #"Duplicata colonna3" = Table.DuplicateColumn(#"Duplicata colonna2", "MESE_RIFERIM", "MESE_RIFERIM - Copia"),
    #"Merge di colonne1" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicata colonna3", {{"MESE_RIFERIM", type text}, {"ANNO_RIFERIM", type text}}, "it-IT"),{"MESE_RIFERIM", "ANNO_RIFERIM"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"COMPETENZA_ECONOMICA"),
    #"CREATA CASSA E COMPETENZA" = Table.TransformColumnTypes(#"Merge di colonne1",{{"COMPETENZA_ECONOMICA", type date}}),
    #"Rinominate colonne1" = Table.RenameColumns(#"CREATA CASSA E COMPETENZA",{{"MESE_RIFERIM - Copia", "MESE_RIFERIM"}, {"ANNO_RIFERIM - Copia", "ANNO_RIFERIM"}, {"ANNO_CEDOLINO - Copia", "ANNO_CEDOLINO"}, {"MESE_CEDOLINO - Copia", "MESE_CEDOLINO"}}),
    #"Modificato tipo2" = Table.TransformColumnTypes(#"Rinominate colonne1",{{"ANNO_CEDOLINO", Int64.Type}, {"MESE_RIFERIM", Int64.Type}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo2", "IMPORTO_U", each if [COD_VOCE]<5000 then [IMPORTO] else[IMPORTENTE]),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"IMPORTO_U", type number}}),
    #"Merge di colonne2" = Table.CombineColumns(#"Modificato tipo1",{"COGNOME", "NOME"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"NOMINATIVO"),
    #"IMPORTATO TIPO_DIP_U" = Table.NestedJoin(#"Merge di colonne2", {"COD_TIPODIP"}, TIPO_DIP, {"COD_TIPODIP"}, "TIPO_DIP", JoinKind.LeftOuter),
    #"Tabella TIPO_DIP espansa" = Table.ExpandTableColumn(#"IMPORTATO TIPO_DIP_U", "TIPO_DIP", {"COD_TIPODIP_U"}, {"TIPO_DIP.COD_TIPODIP_U"}),
    #"IMPORTATO NATURARAPP_U" = Table.NestedJoin(#"Tabella TIPO_DIP espansa", {"COD_NATRAPP"}, NATRAPP, {"COD_NATRAPP"}, "NATRAPP", JoinKind.LeftOuter),
    #"Tabella NATRAPP espansa" = Table.ExpandTableColumn(#"IMPORTATO NATURARAPP_U", "NATRAPP", {"COD_NATRAPP_U"}, {"NATRAPP.COD_NATRAPP_U"}),
    #"IMPORTATO RUOLO_U" = Table.NestedJoin(#"Tabella NATRAPP espansa", {"COD_RUOLO"}, RUOLO, {"COD_RUOLO"}, "RUOLO", JoinKind.LeftOuter),
    #"Tabella RUOLO espansa" = Table.ExpandTableColumn(#"IMPORTATO RUOLO_U", "RUOLO", {"COD_RUOLO_U"}, {"RUOLO.COD_RUOLO_U"}),
    IMPORTATO_AGGREGATORE = Table.NestedJoin(#"Tabella RUOLO espansa", {"COD_VOCE"}, VOGE, {"COD_VOCE"}, "VOGE", JoinKind.LeftOuter),
    #"Tabella VOGE espansa" = Table.ExpandTableColumn(IMPORTATO_AGGREGATORE, "VOGE", {"COD.AGG."}, {"VOGE.COD.AGG."}),
    #"CREATO CODICE CE" = Table.CombineColumns(Table.TransformColumnTypes(#"Tabella VOGE espansa", {{"VOGE.COD.AGG.", type text}}, "it-IT"),{"TIPO_DIP.COD_TIPODIP_U", "NATRAPP.COD_NATRAPP_U", "RUOLO.COD_RUOLO_U", "VOGE.COD.AGG."},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"CODICE_CE")
in
    #"CREATO CODICE CE"
Avatar utente

Enrico Galli
Messaggi: 890 | Topic creati
Iscritto il: dom 28 giu 2020, 19:03
Luogo: San Giovanni in Persiceto (BO)
Ringraziato: 325 volte
Contatta:

INSERIRE DIFFERENZA TRA DUE RIGHE IN POWER QUERY

Messaggio da Enrico Galli »

Ciao. Se i campi che hai oscurato con "xxx" sono identici tra le due righe, potresti:
1) Scomporre la tabella in due parti (una con solo quei due codici filtrati e una con tutti gli altri)
Sulla sola parte con i due codici:
2) Ordini per matricola e per IMPORTO
3) Sostituisci nella colonna importo gli 0 con null
4) Riempi (verso l'alto o verso il basso, dipende da come hai ordinato i record, magari fallo in entrambi i versi) la colonna IMPORTO
5) Filtri (togliere) tutti i record dove IMPORTENTE = 0
6) Sostituisci COD_VOCE e DESCRIZIONE
7) A questo punto avrai i membri da utilizzare nella sottrazione su un'unica riga: calcoli la sottrazione
8) elimini le colonne IMPORTO e IMPORTENTE, ricrei IMPORTO e rinomini la colonna calcolata come IMPORTENTE
9) Accodi una delle due tabelle create al passaggio 1 con il risultato del passaggio 8 (Table.Combine({"Tabella1","Tabella2"}))

Nota che non è necessario duplicare la query: se fai riferimento ai passaggi giusti, puoi anche restare sempre nella stessa. Sono diversi step, ma del resto è un'operazione anomala quella che chiedi di fare ;)
Enrico Galli
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione
Avatar utente

Autore del topic
Alieno86
Messaggi: 47 | Topic creati
Iscritto il: mer 9 giu 2021, 16:36
Ringraziato: 5 volte

INSERIRE DIFFERENZA TRA DUE RIGHE IN POWER QUERY

Messaggio da Alieno86 »

Grazie mille a breve proverò a trasformare le parole in codice.
È un operazione anomala perché la situazione è anomala ma ha un suo senso anche se contorto 😀.
Questo week farò un po' di prove e vi farò sapere com'è andata.
Avatar utente

Autore del topic
Alieno86
Messaggi: 47 | Topic creati
Iscritto il: mer 9 giu 2021, 16:36
Ringraziato: 5 volte

INSERIRE DIFFERENZA TRA DUE RIGHE IN POWER QUERY

Messaggio da Alieno86 »

Ho risolto in un altro modo completamente differente.
Non sarà un sistema elegante ma funziona, mi permetto di scrivere che ho fatto giusto per un opinione o per dare uno spunto a chi leggerà.
Ho creato una colonna condizione che riporta l'importo della colonna importente e se il cod.voce uguale a 5004 lo moltiplica per meno uno.
Ho poi eliminato la colonna importente.
Ho creato una tabellina di raccordo dove le voci 5038 e 5004 riepilogano in una voce 999 e ho ottenuto il risultato che mi serviva.

Grazie del tempo speso
Avatar utente

Enrico Galli
Messaggi: 890 | Topic creati
Iscritto il: dom 28 giu 2020, 19:03
Luogo: San Giovanni in Persiceto (BO)
Ringraziato: 325 volte
Contatta:

INSERIRE DIFFERENZA TRA DUE RIGHE IN POWER QUERY

Messaggio da Enrico Galli »

Non capisco... nel tuo esempio, IMPORTENTE sul codice 5004 è uguale a zero, perciò serve a ben poco moltiplicarlo per -1... :eh: comunque se hai ottenuto il risultato, questo è quello che più conta ;)
Enrico Galli
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione
Rispondi