Apertura fatture XML con PowerQuery con duplicazioni L’argomento è risolto

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

Moderatore: Utilizzo_prof_Excel


Master
Messaggi: 46 | Topic creati
Iscritto il: sab 4 set 2021, 19:13
Ringraziato: 2 volte

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Master »

Buonasera,

il mio obiettivo è aprire una fattura XML grazie a PowerQuery e fare analisi sui dati riportati in fattura.
Nel caso specifico, riporto il file PDF di un fattura, il file XML della stessa e un tentativo che ho fatto in excel.

Nella fattura sono stati inseriti 4 documenti di trasporto ognuno dei quali con diversi prodotti. Le righe di fatturazione dovrebbero essere 17 totali.
Io vorrei ottenere su un excel i seguenti campi: N. documento di trasporto, data DDT, prezzo unitario, quantità, prezzo totale ed IVA. Le altre colonne sono utili ma non indispensabili. Come potete vedere dal file Excel, giocando con il comando "Espandi" di PowerQuery sono riuscito ad estrarre molte informazioni delle righe di fatturazione contenuto dal file XML della fattura elettronica. Tuttavia, con la procedura guidata e giocando con il comando "ESPANDI" PowerQuery quadruplicata le righe perchè ad ogni DDT attacca tutte le 17 righe di prodotto. Si ottengono cosi 17*0 = 68 righe di fatturazione.
Ho tentato anche in altre fatture ma ottengo sempre la stessa duplicazione DDT * Numero righe prodotto arrivando persino a bloccare il PC per il numero elevato di righe.

Chiedo cortesemente come posso raggiungere il mio obiettivo, evitando dannose dulicazioni.
Allegati
fattura_prova.xml
(14 KiB) Scaricato 11 volte
Fattura pdf.pdf
(206.68 KiB) Scaricato 11 volte
Prova.xlsx
(21.08 KiB) Scaricato 13 volte


Avatar utente

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

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Andrea90 »

Master,

Qualcosa di simile:

Codice: Seleziona tutto

let
    Origine = Xml.Tables(File.Contents("C:\Users\Andrea Casadei\Downloads\fattura_prova.xml")),
    #"Rimosse colonne" = Table.RemoveColumns(Origine,{"http://www.w3.org/2001/XMLSchema-instance", "Attribute:versione"}),
    #"Tabella Namespace: espansa" = Table.ExpandTableColumn(#"Rimosse colonne", "Namespace:", {"FatturaElettronicaBody"}, {"FatturaElettronicaBody"}),
    #"Tabella FatturaElettronicaBody espansa" = Table.ExpandTableColumn(#"Tabella Namespace: espansa", "FatturaElettronicaBody", {"DatiGenerali", "DatiBeniServizi"}, {"DatiGenerali", "DatiBeniServizi"}),
    #"Tabella DatiGenerali espansa" = Table.ExpandTableColumn(#"Tabella FatturaElettronicaBody espansa", "DatiGenerali", {"DatiDDT"}, {"DatiDDT"}),
    #"Tabella DatiDDT espansa" = Table.ExpandTableColumn(#"Tabella DatiGenerali espansa", "DatiDDT", {"NumeroDDT", "DataDDT", "RiferimentoNumeroLinea"}, {"NumeroDDT", "DataDDT", "RiferimentoNumeroLinea"}),
    #"Tabella DatiBeniServizi espansa" = Table.ExpandTableColumn(#"Tabella DatiDDT espansa", "DatiBeniServizi", {"DettaglioLinee"}, {"DettaglioLinee"}),
    #"Expanded RiferimentoNumeroLinea" = Table.ExpandTableColumn(#"Tabella DatiBeniServizi espansa", "RiferimentoNumeroLinea", {"Element:Text"}, {"Element:Text"}),
    FilterTab = Table.AddColumn(#"Expanded RiferimentoNumeroLinea", "Test", each Table.SelectRows([DettaglioLinee], (x)=> x[NumeroLinea] = [#"Element:Text"])),
    #"Removed Columns" = Table.RemoveColumns(FilterTab,{"DettaglioLinee"}),
    #"Expanded Test" = Table.ExpandTableColumn(#"Removed Columns", "Test", {"NumeroLinea", "Descrizione", "PrezzoUnitario", "PrezzoTotale", "AliquotaIVA", "CodiceArticolo", "Quantita", "UnitaMisura", "TipoCessionePrestazione"}, {"NumeroLinea", "Descrizione", "PrezzoUnitario", "PrezzoTotale", "AliquotaIVA", "CodiceArticolo", "Quantita", "UnitaMisura", "TipoCessionePrestazione"})
in
    #"Expanded Test"
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

Autore del topic
Master
Messaggi: 46 | Topic creati
Iscritto il: sab 4 set 2021, 19:13
Ringraziato: 2 volte

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Master »

Buongiorno Andrea,

grazie mille per lo scritp.
Su questa fattura funzione ma su altre fatture non funziona correttamente.
Compare un errore di questo tipo: ...

"Expression.Error: Non è possibile convertire il valore "000010" nel tipo Table.
Dettagli:
Value=000010
Type=Type"

Quindi ti chiedo:

1. perchè non riesco ad ottenere i dati con l'autocomposizione mentre con lo script si riesce ? Alla fine lo script non mi sembra impossibile. Vorrei capirlo meglio in modo tale che posso adattarlo a seconda delle fatture che devroi aprire.
2. Che cosa indica nel tuo script il passaggio "FilterTab = Table.AddColumn"? Mi sembra di percepire che quello è un passaggio findamentale per evitare duplucazioni
3. Che cosa indica l'errore, dove dovrei agire per eliminarlo ? L'errore si crea da #"Expanded RiferimentoNumeroLinea"
Avatar utente

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

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Andrea90 »

Master,
Master ha scritto: sab 15 apr 2023, 9:51 1. perchè non riesco ad ottenere i dati con l'autocomposizione mentre con lo script si riesce ? Alla fine lo script non mi sembra impossibile. Vorrei capirlo meglio in modo tale che posso adattarlo a seconda delle fatture che devroi aprire.
Non so cosa sia per te "autocompilazione"...
Master ha scritto: sab 15 apr 2023, 9:51 2. Che cosa indica nel tuo script il passaggio "FilterTab = Table.AddColumn"? Mi sembra di percepire che quello è un passaggio findamentale per evitare duplucazioni
Prima di fare l'espansione della tabella in uno dei tuoi ultimi passaggi vedi che ci sono due campi che contengono, in ciascuna cella, degli oggetti tabella. Nella prima, in ciascuna tabella, ci sono i numeri che fanno riferimento alle linee di dettaglio della fattura. Le righe di dettaglio sono contenute nel secondo campo (non vengono filtrate in automatico e dunque ti ritrovi ad avere, in ciascuna cella, tutte le 17 righe). Dunque quel passaggio che ho fatto semplicemente filtra la tabella di dettaglio per la sola riga di riferimento del primo campo (in questo modo eviti i duplicati che otterresti con la semplice espansione).
Master ha scritto: sab 15 apr 2023, 9:51 3. Che cosa indica l'errore, dove dovrei agire per eliminarlo ? L'errore si crea da #"Expanded RiferimentoNumeroLinea"
Senza vedere il file che riporta l'errore si può andare solo per tentativi, probabilmente in una di quelle celle non si genera un valore tabella e dunque non è in grado di eseguire il comando di Expanded Table.

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

Autore del topic
Master
Messaggi: 46 | Topic creati
Iscritto il: sab 4 set 2021, 19:13
Ringraziato: 2 volte

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Master »

Allora ....
Inizialmente pensavo che tu avessi scritto il codice manualmente.
Poi, ho capito che in realtà non fai così :D

Ho analizzato meglio quello che hai fatto ma non riesco a capire come inserisci il filtro sulle linee di dettaglio...
Forse Hai inserito una colonna personalizzata con un'espressione ?
Avatar utente

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

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Andrea90 »

Master,

Non so quali siano le tue attuali conoscenze in ambito Power Query, ma quello che ho fatto è stato aggiungere una colonna calcolata nella quale ho inserito manualmente il codice che vedi cliccando sul simbolo dell’ingranaggio posto a destra del nome del passaggio a cui fai riferimento.

Power Query è uno strumento ETL che gode di un’interfaccia utente per costruita, e tramite click sui vari pulsanti tramuta le tue azioni in codice M… e la maggior parte delle volte bastano questi click per compiere le operazioni di pulizia necessarie alla base dati.
Ma ciò non toglie che un utente avanzato possa scrivere direttamente in codice M per svolgere operazioni che con i pulsanti non sarebbe possibile ottenere.

Nel tuo caso ho aggiunto una colonna personalizzata con Table.AddColumn() andando a filtrare le tabelle contenute in ciascuna cella tramite la formula Table.SelectRows().

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

Autore del topic
Master
Messaggi: 46 | Topic creati
Iscritto il: sab 4 set 2021, 19:13
Ringraziato: 2 volte

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Master »

Diciamo che le mie conoscenze sono basiche ... Sto imparando, mi esprimo male ma ho intuito.
Se non mi tradisce, la formula a cui ti riferisci è questa.
=Table.SelectRows([DettaglioLinee], (x)=> x[NumeroLinea] = [#"Element:Text"])


Ho provato a rifare il tuo prcedimento ma qualcosa non mi torna.
In allegato c'è uno screenshot, puoi cortesemente darci un'occhiata per farmi capire dove sbaglio ?
Allegati
Immagine 2023-04-15 132344.png
Immagine 2023-04-15 132344.png (120.7 KiB) Visto 118 volte
Avatar utente

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

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Andrea90 »

Master,

A parte la doppia parentesi quadra che racchiude il nome di un campo "RiferimentoNumeroLinea".

Nel mio codice io ho scritto questa condizione "x[NumeroLinea] = [#"Element:Text"]", cosa rappresentava [#"Element:Text"]" nel mio file, e cosa rappresenta invece nel tuo che vedo nell'immagine?

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

Autore del topic
Master
Messaggi: 46 | Topic creati
Iscritto il: sab 4 set 2021, 19:13
Ringraziato: 2 volte

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Master »

ok, ho capito l'errore... Adesso mi funziona !

Nella fattura su cui sto lavorando il campo non chiamava "Element:Text" e quindi non funzionava.
In sintesi, con l'espressione =Table.SelectRows([DettaglioLinee], (x)=> x[NumeroLinea] = [#"Element:Text"]) è come se gli dicessi nella colonna/tabella "DettaglioLinee" seleziona solo le righe che in cui è rispettata la condizione "NumeroLinea" è uguale al numero linea che compare nel campo "Element:Text" della precedente tabela "DettaglioDDT"

Un' ultima domanda.

Guardando la lista dei passaggi applicati mi compare un FilterTab, in cui, dalle immpostazioni leggo c'è l'espressione di Table.SelectRows(). Dove prende l'etichetta FilterTab ?

Grazie mille della competente e preziosa collaborazione
Avatar utente

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

Apertura fatture XML con PowerQuery con duplicazioni

Messaggio da Andrea90 »

Master,

Quello è semplicemente il nome dato al passaggio, PowerQuery ne assegna uno in automatico, ma nulla vieta (e sarebbe in realtà consigliato), di modificarlo per renderlo intuibile anche da chi non ha scritto il codice (o anche a te stesso nel caso in cui un domani tu debba rimettere mano al codice).

Avendo scritto quel passaggio a mano ho assegnato direttamente il nome.

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
Rispondi