Combinare i dati da più origini dati (Power Query)

Importante :  Il presente articolo è stato tradotto automaticamente, vedere la dichiarazione di non responsabilità. Per visualizzare la versione inglese dell'articolo, fare clic qui.

Nota : Power Query si chiama Recupera e trasforma in Excel 2016. Le informazioni di questo articolo si applicano a entrambi. Per altre informazioni,v edere Recupera e trasforma in Excel 2016.

In questa esercitazione si userà dell'Editor di Query di Power Query per importare dati da un file di Excel locale che contiene informazioni sui prodotti e da un feed che OData contiene informazioni sugli ordini prodotti. Si eseguire operazioni di trasformazione e l'aggregazione e combinare i dati da entrambe le origini per generare un report Vendite per prodotto e anno.

Per eseguire questa esercitazione, è necessaria la cartella di lavoro Products and Orders. Nella finestra di dialogo Salva con nome assegnare al file il nome Products and Orders.xlsx.

In questa esercitazione verranno eseguite le operazioni seguenti:

Attività 1: Importare i prodotti in una cartella di lavoro di Excel

Passaggio 1: Connettersi a una cartella di lavoro di Excel

Passaggio 2: Convertire la prima riga in intestazioni di colonna della tabella

Passaggio 3: Rimuovere le altre colonne per visualizzare solo le colonne di interesse

Passaggi di Power Query creati

Passaggio 4: Importare una query per i prodotti

Attività 2: Importare i dati degli ordini da un feed OData

Passaggio 1: Connettersi a un feed OData

Passaggio 2: Espandere una tabella Orders_Details

Espandere il collegamento Table di Order_Details

Passaggio 3: Rimuovere le altre colonne per visualizzare solo le colonne di interesse

Rimuovere le colonne selezionate

Passaggio 4: Calcolare il totale della riga per ogni riga di Order_Details

Calcolare il totale della riga per ogni riga di Order_Details

Passaggio 5: Trasformare una colonna OrderDate in anno

Passaggio 6: Raggruppare le righe per ProductID e Year

Passaggio 7: Rinominare una query

Risultati finali della query

Passaggi di Power Query creati

Passaggio 8: Disabilitare il download delle query in una cartella di lavoro di Excel

Disabilitare un download di query

Attività 3: Combinare le query Products e Total Sales

Passaggio 1: Integrare ProductID in una query Total Sales

Passaggio 2: Espandere una colonna sottoposta a merge

Espandere il collegamento Table di NewColumn

Passaggi di Power Query creati

Passaggio 3: Caricare una query Total Sales per Product in un modello di dati di Excel

Caricare la query Total Sales per Product nel modello di dati di Excel

Query finale Total Sales per Product

Attività 1: Importare i prodotti in una cartella di lavoro di Excel

In questa attività si importano i prodotti dal file Products and Orders file in una cartella di lavoro di Excel.

Passaggio 1: Connettersi a una cartella di lavoro di Excel

  1. Creare una cartella di lavoro di Excel.

  2. Nella scheda della barra multifunzione POWER QUERY fare clic su Da file > Da Excel.

  3. Nella finestra di dialogo Sfoglia di Excel selezionare o cercare il file Products and Orders.xlsx per importare o collegare il file.

  4. Nel riquadro Strumento di navigazione fare doppio clic sul foglio di lavoro Products oppure fare clic su Products e quindi su Modifica query. Quando si modifica una query o ci si connette a una nuova origine dati, viene visualizzata la finestra Editor di query.

    Nota : Per un video rapido su come visualizzare l'editor di query, vedere alla fine di questo articolo.

Passaggio 2: Convertire la prima riga in intestazioni di colonna della tabella

Nella griglia di Query Preview la prima riga della tabella non contiene i nomi delle colonne della tabella. Per convertire la prima riga in intestazioni di colonna della tabella:

  1. Fare clic sull'icona della tabella ( Icona Tabella ) nell'angolo superiore sinistro dell'anteprima dati.

  2. Fare clic su Utilizza la prima riga come intestazioni.

Convertire la prima riga in intestazioni di colonna della tabella

Passaggio 3: Rimuovere le altre colonne per visualizzare solo le colonne di interesse

In questo passaggio verranno rimosse tutte le colonne tranne ProductID, ProductName, CategoryID e QuantityPerUnit.

  1. Nella griglia di Query Preview selezionare le colonne ProductID, ProductName, CategoryID e QuantityPerUnit (usando CTRL+clic o MAIUSC+clic).

  2. Nella barra multifunzione dell'Editor di query fare clic su Rimuovi colonne > Rimuovi altre colonne o fare clic con il pulsante destro del mouse su un'intestazione di colonna e scegliere Rimuovi altre colonne.

    Nascondere le altre colonne

Passaggi di Power Query creati

Quando si eseguono attività di query in Power Query, i passaggi della query vengono creati ed elencati nel riquadro Impostazioni query nell'elenco PASSAGGI APPLICATI. A ogni passaggio della query è associata una formula di Power Query corrispondente, anche nota come linguaggio "M". Per altre informazioni sul linguaggio delle formule di Power Query, vedere Informazioni sulle formule di Power Query.

Attività

Passaggio query

equazione

Connettersi a una cartella di lavoro di Excel

Origine

Origine{[Nome="Products"]}[Data]

Convertire la prima riga in intestazioni di colonna della tabella

FirstRowAsHeader

Table.PromoteHeaders

(Products)

Rimuovere le altre colonne per visualizzare solo le colonne di interesse

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Passaggio 4: Importare una query per i prodotti

In questo passaggio si importerà la query Products nella cartella di lavoro di Excel.

  1. Nella barra multifunzione dell'Editor di query fare clic su Applica e chiudi. I risultati verranno visualizzati in un nuovo foglio di lavoro di Excel.

Inizio pagina

Attività 2: Importare i dati degli ordini da un feed OData

In questa attività si importano dati nella cartella di lavoro di Excel dal feed OData Northwind di esempio all'indirizzo http://services.odata.org/Northwind/Northwind.svc.

Passaggio 1: Connettersi a un feed OData

  1. Sulla scheda della barra multifunzione POWER QUERY fare clic su Da altre origini > Da feed OData.

  2. Nella finestra di dialogo Feed OData immettere l'URL per il feed OData Northwind.

  3. Fare clic su OK.

  4. Nel riquadro Strumento di navigazione fare doppio clic sulla tabella Orders o fare clic su Orders e quindi su Modifica.

Nota :  Quando si passa il mouse su una tabella, verrà visualizzato un riquadro a comparsa di anteprima.

Passare il mouse su un'origine dati

Passaggio 2: Espandere una tabella Orders_Details

In questo passaggio si espanderà la tabella Order_Details correlata alla tabella Orders per combinare le colonne ProductID, UnitPrice e Quantity di Order_Details nella tabella Orders. L'operazione Espandi consente di combinare le colonne da una tabella correlata in una tabella in base all'argomento. Quando la query viene eseguita, le righe dalla tabella correlata (Order_Details) vengono combinate nelle righe dalla tabella in base all'argomento (Orders).

In Power Query una colonna che contiene un collegamento a una tabella correlata ha un collegamento Record o Table. Un collegamento Record consente di passare a un singolo record correlato e rappresenta una relazioneuno-a-uno con una tabella in base all'argomento. Un collegamento Table consente di passare a una tabella correlata e rappresenta una relazione uno-a-molti con una tabella in base all'argomento. Un collegamento rappresenta le proprietà di navigazione in un'origine dati all'interno di un modello relazionale. Per un feed OData, le proprietà di navigazione rappresentano un'entità con un'associazione di chiave esterna. In un database, ad esempio SQL Server, le proprietà di navigazione rappresentano le relazioni di chiave esterna nel database.

Espandere il collegamento Table di Order_Details

Dopo l'espansione della tabella Order_Details vengono visualizzate tre nuove colonne e vengono aggiunte altre righe alla tabella Orders una per ogni riga nella tabella annidata o correlata.

  1. Nel riquadro di Query Preview scorrere fino alla colonna Order_Details.

  2. Nella colonna Order_Details fare clic sull'icona di espansione ( Espandi ).

  3. Nell'elenco a discesa Espandi:

    1. Fare clic su (Seleziona tutte le colonne) per deselezionare tutte le colonne.

    2. Fare clic su ProductID, UnitPrice e Quantity.

    3. Fare clic su OK.

      Espandere il collegamento Table di Order_Details

      Nota :  In Power Query è possibile espandere le tabelle collegate da una colonna ed eseguire operazioni di aggregazione sulle colonne della tabella collegata prima di espandere i dati nella tabella in base all'argomento. Per altre informazioni su come eseguire operazioni di aggregazione, vedere Aggregare i dati da una colonna.

Passaggio 3: Rimuovere le altre colonne per visualizzare solo le colonne di interesse

In questo passaggio verranno rimosse tutte le colonne tranne OrderDate, ProductID, UnitPrice e Quantity. Nell'attività precedente si è usata l'opzione Rimuovi altre colonne. In questa attività si procederà alla rimozione delle colonne selezionate.

Rimuovere le colonne selezionate

  1. Nel riquadro di Query Preview selezionare tutte le colonne:

    1. Fare clic sulla prima colonna (OrderID).

    2. Premere MAIUSC+clic sull'ultima colonna (Shipper).

    3. Selezionare le colonne OrderDate, Order_Details.ProductID, Order_Details.UnitPrice e Order_Details.Quantity premendo CTRL+clic.

  2. Fare clic con il pulsante destro del mouse sull'intestazione delle colonne selezionate e quindi scegliere Rimuovi colonne.

Passaggio 4: Calcolare il totale della riga per ogni riga di Order_Details

In questo passaggio verrà creata una Colonna personalizzata per calcolare il totale della riga per ogni riga di Order_Details.

Calcolare il totale della riga per ogni riga di Order_Details

  1. Nel riquadro Query Preview fare clic sull'icona della tabella ( Icona Tabella ) nell'angolo superiore sinistro dell'anteprima.

  2. Fare clic su Inserisci colonna > Personalizzata.

  3. Nella finestra di dialogo Inserisci colonna personalizzata, nella casella di testo Formula colonna personalizzata immettere [Order_Details.UnitPrice] * [Order_Details.Quantity].

  4. Nella casella di testo Nuovo nome di colonna digitare Line Total.

  5. Fare clic su OK.

Calcolare il totale della riga per ogni riga di Order_Details

Passaggio 5: Trasformare una colonna OrderDate in anno

In questo passaggio si procederà alla conversione della colonna OrderDate per visualizzare l'anno della data dell'ordine.

  1. Nella griglia Anteprima fare clic con il pulsante destro del mouse sulla colonna OrderDate e quindi scegliere Trasforma > Year.

  2. Rinominare la colonna OrderDate in Year:

    1. Fare doppio clic sulla colonna OrderDate e digitare Year oppure

    2. Fare clic con il pulsante destro del mouse sulla colonna OrderDate, scegliere Rinomina e quindi digitare Year.

Passaggio 6: Raggruppare le righe per ProductID e Year

  1. Nella griglia di Query Preview selezionare Year e Order_Details.ProductID.

  2. Fare clic con il pulsante destro del mouse su una delle intestazioni e quindi scegliere Raggruppa per.

  3. Nella finestra di dialogo Raggruppa per:

    1. Nella casella di testo Nuovo nome di colonna digitare Total Sales.

    2. Nell'elenco a discesa Operazione selezionare Somma.

    3. Nell'elenco a discesa Colonna selezionare Line Total.

  4. Fare clic su OK.

    Finestra di dialogo Raggruppa per per le operazioni di aggregazione

Passaggio 7: Rinominare una query

Prima di importare i dati delle vendite in Excel, assegnare alla query il nome Total Sales:

  1. Nel riquadro Impostazioni query, nella casella Nome digitare Total Sales.

Risultati finali della query

Dopo avere eseguito ogni passaggio, sarà disponibile una query Total Sales sul feed OData Northwind.

Total Sales

Passaggi di Power Query creati

Quando si eseguono attività di query in Power Query, i passaggi della query vengono creati ed elencati nel riquadro Impostazioni query nell'elenco PASSAGGI APPLICATI. A ogni passaggio della query è associata una formula di Power Query corrispondente, anche nota come linguaggio "M". Per altre informazioni sul linguaggio delle formule di Power Query, vedere Informazioni sulle formule di Power Query.

Attività

Passaggio query

equazione

Connettersi a un feed OData

Origine

Origine{[Name="Orders"]}[Data]

Espandere la tabella Order_Details

Espandere Order_Details

Table.ExpandTableColumn

(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

Rimuovere le altre colonne per visualizzare solo le colonne di interesse

RemovedColumns

Table.RemoveColumns

(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

Calcolare il totale della riga per ogni riga di Order_Details

InsertedColumns

Table.AddColumn

(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Trasformare la colonna OrderDate per visualizzare l'anno

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

Raggruppare le righe per ProductID e Year

GroupedRows

Table.Group
(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Passaggio 8: Disabilitare il download delle query in una cartella di lavoro di Excel

Poiché la query Total Sales non rappresenta il report sulle vendite complessive per prodotto e anno, è necessario disabilitare il download della query nella cartella di lavoro di Excel. Quando l'opzione Carica in foglio di lavoro è impostata su Disattiva nel riquadro Impostazioni query, i risultati dei dati di questa query non vengono scaricati ma possono comunque essere combinati con altre query per generare il risultato desiderato. Nell'attività successiva si apprenderà a combinare questa query con la query Products.

Disabilitare un download di query

  1. Nel riquadro Impostazioni query deselezionare Carica in foglio di lavoro .

  2. Nella barra multifunzione dell'Editor di query fare clic su Applica e chiudi. Nel riquadro Query della cartella di lavoro per la query Total Sales è visualizzato Caricamento disabilitato.

    Disabilitare un download di query

Inizio pagina

Attività 3: Combinare le query Products e Total Sales

Power Query consente di combinare più query mediante merge o accodamento. L'operazione Merge viene eseguita su qualsiasi query di Power Query sotto forma di tabella indipendentemente dall'origine dati da cui provengono i dati. Per altre informazioni sulla combinazione di origini dati, vedere Combinare più query.

In questa attività verranno combinate le query Products e Total Sales usando un passaggio di query Merge e Espandi.

Passaggio 1: Integrare ProductID in una query Total Sales

  1. Nella cartella di lavoro di Excel passare alla query Products nel Foglio2.

  2. Nella scheda della barra multifunzione QUERY fare clic su Merge.

  3. Nella finestra di dialogo Merge selezionare Products come tabella principale e Total Sales come query secondaria o correlata per l'operazione merge. Total Sales diventerà una nuova colonna espandibile.

  4. Per abbinare Total Sales a Products in base al valore di ProductID, selezionare la colonna ProductID dalla tabella Products e la colonna Order_Details.ProductID dalla tabella Total Sales.

  5. Nella finestra di dialogo Livelli di privacy:

    1. Selezionare Organizzativo come livello di isolamento della privacy per entrambe le origini dati.

    2. Fare clic su Salva.

  6. Fare clic su OK.

    Nota sulla sicurezza :  I Livelli di privacy impediscono a un utente di combinare accidentalmente i dati da più origini dati che potrebbero essere private o organizzative. A seconda della query, un utente potrebbe inviare accidentalmente i dati dall'origine dati privata a un'altra origine dati che potrebbe essere dannosa. Power Query analizza ogni origine dati e la classifica nel livello di privacy definito: Pubblico, Organizzativo e Privato. Per altre informazioni sui livelli di privacy, vedere Livelli di privacy.

    Finestra di dialogo Merge

Dopo avere fatto clic su OK, l'operazione Merge crea una query. Il risultato della query contiene tutte le colonne dalla tabella principale (Products) e una singola colonna contenente un collegamento di navigazione alla tabella correlata (Total Sales). L'operazione Espandi permette di aggiungere nuove colonne nella tabella principale o nella tabella in base all'argomento dalla tabella correlata.

Risultato operazione Merge

Passaggio 2: Espandere una colonna sottoposta a merge

In questo passaggio si procederà all'espansione della colonna sottoposta a merge con il nome NewColumn per creare due nuove colonne nella query Products: Year e Total Sales.

Espandere il collegamento Table di NewColumn

  1. Nella griglia di Query Preview fare clic sull'icona di espansione accanto a NewColumn ( Espandi ).

  2. Nell'elenco a discesa Espandi:

    1. Fare clic su (Seleziona tutte le colonne) per deselezionare tutte le colonne.

    2. Fare clic su Year e Total Sales.

    3. Fare clic su OK.

  3. Assegnare a queste due colonne i nomi Year e Total Sales.

  4. Selezionare Ordine decrescente per Total Sales per individuare i prodotti e gli anni che hanno registrato il volume di vendite più elevato.

  5. Rinominare la query in Total Sales per Product.

Espandere il collegamento Table

Passaggi di Power Query creati

Man mano che si eseguono le attività di query Merge in Power Query, i passaggi vengono creati ed elencati nel riquadro Impostazioni query nell'elenco PASSAGGI APPLICATI A ogni passaggio della query è associata una formula di Power Query corrispondente, anche nota come linguaggio "M". Per altre informazioni sul linguaggio delle formule di Power Query, vedere Informazioni sulle formule di Power Query.

Attività

Passaggio query

equazione

Integrare ProductID in una query Total Sales

Origine (origine dati per l'operazione Merge)

Table.NestedJoin

(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")

Espandere una colonna sottoposta a merge

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

Passaggio 3: Caricare una query Total Sales per Product in un modello di dati di Excel

In questo passaggio verrà disattivata l'opzione Carica in foglio di lavoro e si caricherà una query nel modello di dati di Excel per generare un report connesso al risultato della query. Oltre ad aggiungere i risultati della query in un foglio di lavoro di Excel, Power Query consente di caricare il risultato della query in un modello di dati di Excel. Dopo avere caricato i dati nel modello di dati di Excel, è possibile usare PowerPivot e Power View per eseguire ulteriori analisi sui dati.

Caricare la query Total Sales per Product nel modello di dati di Excel

  1. Nel riquadro Impostazioni query deselezionare Carica in foglio di lavoro e selezionare Carica nel modello di dati.

  2. Per caricare la query nel modello di dati di Excel, fare clic su Applica e chiudi.

Caricare il modello di dati di Excel

Query finale Total Sales per Product

Dopo avere eseguito tutti i passaggi, si otterrà una query Total Sales per Product che combina i dati provenienti dal file Products and Orders.xlsx e dal feed OData Northwind. Questa query può essere applicata a un modello di PowerPivot. Le eventuali modifiche apportate alla query in Power Query si riflettono nella tabella risultante nel modello di PowerPivot.

Inizio pagina

Nota : L'editor di query viene visualizzato solo quando si carica, modifica o crea una nuova query con Power Query. Il video seguente illustra la finestra Editor di query che viene visualizzata dopo la modifica di una query in una cartella di lavoro di Excel. Per visualizzare l'editor di query senza caricare o modificare una query esistente in una cartella di lavoro, nella sezione Recupera dati esterni della scheda Power Query sulla barra multifunzione selezionare Da altre origini > Query vuota. Il video seguente illustra uno dei modi disponibili per visualizzare la finestra Editor di query.

Come visualizzare l'Editor di query in Excel

Nota : Dichiarazione di non responsabilità per la traduzione automatica: Il presente articolo è stato tradotto tramite un software di traduzione automatica e non da una persona. Microsoft offre le traduzioni automatiche per consentire a coloro che non conoscono la lingua inglese di leggere gli articoli sui prodotti, sui servizi e sulle tecnologie Microsoft. Dal momento che l'articolo è stato tradotto automaticamente, potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli.

Amplia le tue competenze
Esplora i corsi di formazione
Ottieni in anticipo le nuove caratteristiche
Partecipa al programma Office Insider

Queste informazioni sono risultate utili?

Grazie per i tuoi commenti e suggerimenti

Grazie per il tuo feedback! Potrebbe essere utile metterti in contatto con uno dei nostri operatori del supporto di Office.

×