Esercitazione su Microsoft Power Query su come risolvere i problemi di larghezza delle colonne fisse miste

Mudassir: Per oggi, abbiamo un problema molto interessante con cui lavorare. Il problema con questo file è che è stato risolto delimitato da colonne e non so come risolverlo utilizzando Microsoft Power Query. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Innanzitutto, non è stato facile per me eliminare le colonne in modo dinamico. In secondo luogo, in questo rapporto abbiamo una tabella con una larghezza di colonna diversa e poi un'altra tabella con una larghezza di colonna diversa.

Quindi, se applico dinamicamente un delimitatore in alto, non sarei in grado di ottenere i dati in modo ordinato. Voglio ottenere tutti questi dati dalla seconda tabella e i miei numeri di prodotto dalla prima tabella. Voglio anche il numero del lavoro in ogni riga di tutte le tabelle.

Ho provato a risolverlo da solo, ma poiché questo ha qualcosa a che fare con la query di potenza avevo bisogno dell'aiuto di Melissa. Pensavo che le ci sarebbero voluti almeno due giorni, ma è riuscita a trovare subito una soluzione.

Melissa ci mostrerà come ha risolto questo complicato problema. Penso che la maggior parte delle persone avrà a che fare con questo tipo di problemi e sta cercando modi per risolverli.

Melissa: Il primo suggerimento è che se stai guardando un file di lunghezza fissa, puoi andare alla scheda Visualizza e attivare l' opzione A spaziatura fissa .

Possiamo vedere che si tratta di un carattere a lunghezza fissa. Possiamo anche vedere le intestazioni, le tabelle iniziali e le sottotabelle. Queste sono le parti che ci interessano e da cui vogliamo estrarre.

Inoltre, assicurati di avere la barra della formula abilitata. È sempre una buona cosa averlo visibile sullo schermo poiché lo useremo frequentemente per apportare lievi modifiche all'input.

Ho creato un parametro per il percorso del file in cui ho archiviato il file CSV. L'ho portato come file di staging e l'ho aggiunto al mio parametro di posizione del file. Quindi ho creato un riferimento e lavorerò da quel riferimento. Quindi questo è ciò che stiamo guardando in questo momento in Microsoft Power query.

Sommario

• Aggiunta di una colonna di indice
• Creazione di un elenco di buffer in Microsoft Power Query
• Separare le intestazioni dalle righe
• Rimozione di valori null e spazi vuoti in Microsoft Power Query
• Dividere le sezioni
• Tagliare le stringhe di testo in Microsoft Power Query
• Conclusione

Aggiunta di una colonna di indice

Di solito, quando inizio a lavorare su un file come questo, ho bisogno di conoscere i requisiti del cliente. Chiedo di cosa ha bisogno il cliente e cosa cercare.

In questo caso, vogliamo il numero dell'articolo e il numero del lavoro dalle intestazioni, quindi vogliamo tutti i dettagli che appartengono a quella specifica intestazione.

Avremo bisogno di una chiave per riunire quelle cose. Ma se non è presente alcuna chiave, allora il mio go-to è aggiungere un index column . Faccio clic sull'icona della mini tabella, seleziono Aggiungi colonna indice , quindi aggiungo Da 0 .

Userò una logica in seguito con le liste, che hanno l'indice a base 0. Avere il tuo indice che inizia da zero in realtà significa che puoi fare riferimento alla stessa riga. Altrimenti, dovrai sottrarre 1 per arrivare a quella posizione in base 0.

Quindi, dobbiamo trovare la posizione in cui si trovano le nostre intestazioni, cosa che possiamo fare abbastanza facilmente perché quelle intestazioni vengono ripetute costantemente nell'intero file.

Per iniziare, copiamo questo valore:

Aggiungi una nuova query vuota, incollala e chiamala HeaderID .

Farò lo stesso processo per le sottotabelle. Copierò quella stringa di testo, creerò un'altra query vuota e incollerò quel valore. Questa sarà la stringa che useremo quando cercheremo righe dettagliate.

Se questo processo modifica in qualche modo l'intestazione di una qualsiasi di queste tabelle, tutto ciò che devo fare è modificare una delle stringhe di testo e il file funzionerà di nuovo.

Non devo davvero immergermi nel codice M per cercare quella stringa che stiamo cercando. Possiamo semplicemente usarlo come parametro.

Abilitiamo il caricamento per queste due query.

Creazione di un elenco di buffer in Microsoft Power Query

La prima cosa che farò è trasformare Column1 in un elenco facendo riferimento e caricandolo in memoria una volta. In questo modo, non devo effettuare chiamate ripetute al file.

Aprirò l'editor avanzato e lo posizionerò completamente in alto. Quando utilizzi l'interfaccia utente per creare il tuo codice, farà riferimento al passaggio precedente.

Quando posizioni un passaggio del buffer in qualsiasi altra parte del tuo codice e desideri apportare una modifica, in un secondo momento, ti aiuterà ad apportare le modifiche al passaggio che stai creando manualmente.

Chiamerò questo BufferList e farò riferimento a Column1. Per caricarlo in memoria, aggiungerò un passaggio List.Buffer .

Ecco la mia variabile fino in cima. Posso fare riferimento più e più volte.

La prima cosa che voglio determinare è dove iniziano le mie intestazioni perché ho bisogno di una chiave per mantenere queste sezioni di intestazione e ottenere un singolo valore per tutte quelle righe. Per fare ciò, aggiungerò una colonna personalizzata e la chiamerò Header .

Scriverò che se Column1 è uguale al nostro ID intestazione, allora voglio che il mio numero di indice sia nullo.

Di conseguenza, ha trovato il testo e ha restituito 5 e 23.

Ho bisogno di quel valore in tutte le righe, quindi devo riempirlo. Puoi semplicemente fare clic con il pulsante destro del mouse per riempire, ma puoi anche utilizzare una sintassi molto semplice e aggiungerla nella barra della formula.

In questo caso ho aggiunto Table.FillDown e nella stringa di testo ho indicato quale colonna vogliamo riempire (Header).

Ora, l'abbiamo riempito per tutte le righe. Abbiamo una chiave per tutte le sezioni di intestazione e tutte le sezioni di riga perché condividono tutte questo valore.

Separare le intestazioni dalle righe

Il passaggio successivo consiste nel dividere le intestazioni dalle righe. Aggiungerò un'altra colonna personalizzata e la chiamerò Temp . Questa volta faremo qualcosa di più elaborato e sfrutteremo quella BufferList che ho creato in precedenza.

Useremo un paio di funzioni liste per esaminare ciascuna delle posizioni e scoprire se c'è una corrispondenza con l'indice.

Inizierò con un'istruzione if e utilizzerò List.Contains per cercare una posizione specifica in BufferList e fare riferimento alla query HeaderID .

Vogliamo trovarlo per l'intera lunghezza del file e quindi restituire la posizione dell'elemento all'interno dell'elenco. Se corrisponde all'indice, abbiamo una corrispondenza per quella riga specifica.

Quindi voglio restituire un valore per identificare l'intestazione. In questo caso, sto solo restituendo una H. Copierò la sintassi in modo da non dover riscrivere tutto da capo.

Dobbiamo anche identificare la sezione della riga. Se l'elenco non contiene HeaderID , ma DetailID , allora siamo su una sezione di riga.

Se Column1 è una stringa di testo vuota, voglio che rimanga empty . In caso contrario, voglio che sia null .

Questa opposizione ha ottenuto la riga di intestazione e ha restituito una H, quindi ha trovato una riga dettagliata e ha restituito una R. Quindi ha restituito 0 per tutti gli elementi condivisi all'interno di quella sezione di riga.

Questi spazi vuoti o nulli sono importanti perché ti consentono di riempire. Il riempimento verso il basso non si sposterà tra quelle celle vuote, quindi possiamo eliminarle in seguito.

Lo faremo nella barra della formula e utilizzeremo di nuovo Table.FillDown . Vuole un elenco con il nome della colonna, che è la nostra colonna Temp .

Ora abbiamo i valori H e R ripetuti in tutta questa colonna, il che significa che possiamo effettivamente dividere le intestazioni dalle sezioni dettagliate.

Puoi anche riempirlo dall'interfaccia utente se non vuoi scrivere il codice. Puoi semplicemente fare clic con il pulsante destro del mouse e selezionare Fill , quindi Down .

Rimozione di valori null e spazi vuoti in Microsoft Power Query

Ora che abbiamo questo diritto, possiamo eliminare le cose di cui non abbiamo bisogno. Tutto ciò che è nullo o contiene uno spazio vuoto sono le righe di cui non abbiamo bisogno e che devono essere rimosse. Possiamo eliminarli filtrando.

Dividere le sezioni

Una volta rimossi quegli spazi vuoti e nulli, ci rimane tutto ciò di cui abbiamo bisogno. A questo punto, possiamo semplicemente dividere le sezioni. Possiamo concentrarci sulle righe di intestazione e selezionarle perché hanno una spaziatura separata da tutte le righe di dettaglio (che hanno anche una spaziatura separata).

Aggiungerò un nuovo passaggio nella barra della formula che mi consente di creare un altro filtro sulla stessa colonna. In questo caso, manterrò solo tutte le sezioni dell'intestazione.

Ora, ho tutte quelle righe di intestazione qui.

Posso selezionare Column1 , andare alla barra della formula, selezionare Dividi colonna , quindi dividere per posizioni.

Quindi chiedi a Power query stessa di capirlo. Suggerirà un paio di posizioni. Fare clic su OK per accettare tali posizioni.

Le uniche cose che ci interessano dalle intestazioni sono l' articolo e il numero di lavoro .

All'interno della barra della formula qui, posso rinominare quelli con Item e Job # . Questo mi salverà da un altro passaggio di ridenominazione della colonna.

Dopo questo passaggio, tutto ciò che devo fare è selezionare Item , selezionare Job # e, naturalmente, selezionare la nostra chiave di intestazione . Quindi rimuoverò tutte le altre colonne perché non mi servono più.

Questo sarà il risultato. Dobbiamo ancora ripulire i valori e rimuovere l'elemento di testo e i trattini. Tutto ciò che vogliamo sono quei valori intermedi.

Quindi apriamo questo e deselezionare i trattini e gli elementi.

Ora, tutte le intestazioni sono pronte.

Dobbiamo eseguire lo stesso processo anche per DetailID . Dovrò rinominare quei passaggi in modo che sia più facile per me tornarci un po' più tardi.

Torneremo alla query iniziale che abbiamo iniziato. Abbiamo iniziato con Righe filtrate nel riquadro Passaggi applicati.

Lo copierò e lo aggiungerò al mio filtro. Questa volta non seleziono H ma seleziono R.

Quindi selezionerò Colonna1, vado alla colonna Dividi, dividi per posizioni, quindi faccio in modo che la query di potenza lo capisca.

Questo è ciò che suggerisce la query di alimentazione. Facciamo un tentativo.

Questo in realtà sembra piuttosto buono. Anche le righe totali si dividono perfettamente. Certo, ci sono molti spazi perché avevamo quel rientro.

Tagliare le stringhe di testo in Microsoft Power Query

Selezionerò la prima colonna, quindi premo Giù + Maiusc per selezionare fino a Colonna 1.10. Vai a Trasforma , seleziona Formato , quindi Taglia . Il taglio rimuoverà solo gli spazi in eccesso davanti o alla fine della corda, non in mezzo.

Successivamente, possiamo semplicemente promuovere le intestazioni, quindi non devo digitare tutte le intestazioni oi titoli per queste colonne. Nel passaggio diviso, ho rinominato due colonne. Ora, ovviamente, con 10 colonne, è un po' una seccatura.

Dobbiamo anche sbarazzarci di quei valori in eccesso. Dato che abbiamo i totali, devo usare una di queste ultime tre colonne perché sono le uniche righe che hanno i valori aggiuntivi da qualche parte nel mezzo. Quindi deselezionare quegli spazi vuoti, trattini e testi.

Quindi rimuoverò le colonne non necessarie in modo che tutto ciò che rimane sia una tabella con solo le intestazioni e solo i dettagli. Abbiamo bisogno di una chiave per riunire di nuovo quelle sezioni.

Per questo, possiamo usare un'unione automatica in modo da poter unire la tabella con se stessa per riunire le informazioni. Nella scheda Home selezionare Unisci , quindi selezionare Colonna 5 e la stessa query.

Invece di AllDetails, voglio AllHeaders come tabella iniziale con cui voglio unirmi.

Ciò ha riportato tutte le informazioni dalla tabella di intestazione con una singola riga per ogni articolo e ogni numero di lavoro.

Abbiamo usato una chiave per unire con le righe di dettaglio. Se premo di lato nello spazio bianco qui, vedremo un'anteprima di tutte le righe che appartengono all'intestazione 5.

Rimuoveremo la colonna finale qui e quindi avremo finito di correggere la larghezza della colonna fissa mista in Microsoft Power query.

Conclusione

In questo tutorial, abbiamo trovato un modo per risolvere i problemi di larghezza fissa delle colonne miste utilizzando Microsoft Power Query. Se ti sono piaciuti i contenuti trattati in questo particolare tutorial, non dimenticare di iscriverti al canale TV LuckyTemplates.

Abbiamo un'enorme quantità di contenuti che escono continuamente da me e da una serie di creatori di contenuti, tutti dedicati a migliorare il modo in cui utilizzi LuckyTemplates e Power Platform.

Melissa