Microsoft Excel - Power Query - Converti codici prodotto

Situazione

Questa è la situazione tipicamente si presenta quando all'interno di un'azienda si cambia il gestionale. Il vecchio codice del prodotto viene cambiato in base a nuovi criteri del nuovo gestionale.

Esempio vecchio codice

1. MI-CAT-001-NERO-SML
2. MI-CAT-001-BLU-XL

diventa nuovo codice

1. MI-ELEC-AUDIO-01-NERO-SML
2. MI-ELEC-AUDIO-01-BLU-XL

Quindi solo CAT-001 diventa ELEC-AUDIO-01

Problema

Abbiamo un elenco di prodotti con i vecchi codici e abbiamo bisogno di sostituirli con la nuova codifica. Vogliamo creare un progetto che 

• legge il file contenente i prodotti
• legge il file con le traduzioni da vecchio pattern nuovo pattern

Soluzione con Excel e Power Query

Useremo il file allegati

Creiamo innanzitutto una cartella che contiene i files

• prodotti.csv

• traduzione.csv

• converti-codici-prodotto.xlsx

  Il progetto in Excel vuoto

Il contenuto sarà quindi

Importiamo i file

Andiamo su Dati > Recupera dati > Da file > Da testo/CSV

e iniziamo a importare i prodotti.csv

Confermiamo con Trasforma dati.

Non abbiamo cose particolari da fare e confermiamo con Chiudi e carica > Chiudi e carica in..

in questa fase creiamo solo la connessione. Confermiamo con OK

Importiamo ora i traduzione.csv con la stessa modalità.

Rendiamo la prima riga come intestazione: Usa la prima riga come intestazione.

otteniamo

e anche per questa tabella creiamo solo una connessione.

Avremo quindi solo le due connessioni:

Creazione della funzione di traduzione "fnTraduciCodice"

Il file fnTraduciCodice.txt contiene una funzione in codice M di Power Query che ci permette di effettuare la traduzione.

La funzione ha 2 argomenti

• la colonna che contiene il codice su cui effettuare la sostituzione
• la Query che contiene la tabella di traduzione con i campi: vecchio, nuovo

Per creare la funzione andiamo su Dati > Recupera dati > Da altre origini > Query vuota

Passiamo all'editor avanzato

Cancelliamo tutto il contenuto preimpostato e incolliamo il contenuto del file fnTraduciCodice.txt. 

Confermiamo con FATTO

Rinominiamo la Query con fnTraduciCodice.

Utilizzo della funzione creata

Torniamo sulla Query prodotti e andiamo in Aggiungi colonna > Richiama funzione personalizzata.

Compiliamo i campi

• Nome nuova colonna: Nuovo codice prodotto
• Query della funzione: fnTraduciCodice
• vecchio_codice: codice-prodotto
• tabella_traduzione: traduzione

Confermiamo con OK

Se ci vengono richieste Le informazioni sulla privacy dei dati facciamo click su Continua e ignoriamo i controlli dei livelli di privacy.

Facciamo click su Salva.

Otteniamo così la nuova colonna che rendiamo di tipo testo

Avvicinando le due colonne possiamo vedere come il vecchio codice è stato trasformato in quello nuovo

Terminiamo facendo click sul pulsante grande Chiudi e carica

Ottenere la tabella dei prodotti aggiornata

Con il tasto destro facciamo click sulla Query prodotti e scegliamo Carica in..

scegliamo 

• In una tabella
• In un nuovo foglio di lavoro

Otteniamo il nostro foglio prodotti

Vantaggio di questa soluzione

Utilizzando 

• una funzione 
• e una tabella di traduzione 

Possiamo 

• effettuare la conversione su più colonne
• usare differenti file di traduzione per la stessa colonna.