Informazioni su come combinare più origini dati (Power Query)

In questa attività si importano prodotti dal file Products and Orders.xlsx (scaricato e rinominato sopra) in una cartella di lavoro di Excel, si alzano le righe a intestazioni di colonna, si rimuovono alcune colonne e si carica la query in un foglio di lavoro.

Passaggio 1: Connettersi a una cartella di lavoro di Excel

1. Creare una cartella di lavoro di Excel.

2. Selezionare Dati > Recupera dati > da file > da cartella di lavoro.

3. Nella finestra di dialogo Importa dati individuare e individuare il file Products.xlsx scaricato, quindi selezionare Apri.

4. Nel riquadro Strumento di navigazione fare doppio clic sulla tabella Prodotti . Viene visualizzata la Editor di queryDi alimentazione.

Passaggio 2: Esaminare i passaggi della query

Per impostazione predefinita, Power Query aggiunge automaticamente diversi passaggi per comodità. Esaminare ogni passaggio in Passaggi applicati nel riquadro Impostazioni query per altre informazioni.

1. Fare clic con il pulsante destro del mouse sul passaggio Origine e scegliere Modifica impostazioni. Questo passaggio è stato creato durante l'importazione della cartella di lavoro.

2. Fare clic con il pulsante destro del mouse sul passaggio Spostamento e scegliere Modifica impostazioni. Questo passaggio è stato creato quando è stata selezionata la tabella nella finestra di dialogo Spostamento .

3. Fare clic con il pulsante destro del mouse sul passaggio Tipo modificato e scegliere Modifica impostazioni. Questo passaggio è stato creato da Power Query che ha dedotto i tipi di dati di ogni colonna. Selezionare la freccia in giù a destra della barra della formula per visualizzare la formula completa.

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. In Anteprima dati selezionare le colonne ProductID, ProductName, CategoryID e QuantityPerUnit (usare CTRL+clic o MAIUSC+clic).

2. Selezionare Rimuovi colonne > Rimuovi altre colonne.

   

Passaggio 4: Caricare la query dei prodotti

In questo passaggio la query Products viene caricata in un foglio di lavoro di Excel.

• Selezionare Home > Chiudi & Carica. La query viene visualizzata in un nuovo foglio di lavoro di Excel.

Riepilogo: Power Query passaggi creati in Attività 1

Mentre 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 sulle formule Power Query, vedere Creare formule Power Query in Excel.

Attività	Passaggio query	Formula
Importare una cartella di lavoro di Excel	Origine	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Selezionare la tabella Prodotti	Esplora	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query rileva automaticamente i tipi di dati di colonna	Modificato tipo	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})
Rimuovere le altre colonne per visualizzare solo le colonne di interesse	Rimosse altre colonne	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

In questa attività i dati vengono importati nella cartella di lavoro di Excel dal feed OData Northwind di esempio in http://services.odata.org/Northwind/Northwind.svc,espandere la tabella Order_Details, rimuovere colonne, calcolare il totale di una riga, trasformare una dataordine, raggruppare le righe per IDProdotto e Anno, rinominare la query e disabilitare il download della query nella cartella di lavoro di Excel.

Passaggio 1: Connettersi a un feed OData

1. Selezionare Dati > Recupera dati> da altre origini > da feed OData.

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

3. Selezionare OK.

4. Nel riquadro Strumento di navigazione fare doppio clic sulla tabella Ordini .

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 della tabella correlata (Order_Details) vengono combinate in righe con la tabella principale (Ordini).

In Power Query una colonna contenente una tabella correlata contiene il valore Record o Table nella cella. Si tratta delle cosiddette colonne strutturate. Record indica un singolo record correlato e rappresenta unarelazione uno-a-uno con i dati o la tabella principale corrente. Tabella indica una tabella correlata e rappresenta una relazione uno-a-molti con la tabella corrente o principale. Una colonna strutturata rappresenta una relazione in un'origine dati con un modello relazionale. Ad esempio, una colonna strutturata indica un'entità con un'associazione di chiave esterna in un feed OData o una relazione di chiave esterna in un database SQL Server.

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. In Anteprima dati scorrere orizzontalmente fino alla colonna Order_Details .

2. Nella colonna Order_Details selezionare l'icona di espansione ().

3. Nell'elenco a discesa Espandi:

   1. Selezionare (Seleziona tutte le colonne) per cancellare tutte le colonne.

   2. Selezionare IDProdotto, PrezzoUnitario e Quantità.

   3. Selezionare OK.

      

      Nota: In Power Query è possibile espandere le tabelle collegate da una colonna e aggregare le 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. 

1. In Anteprimadati selezionare le colonne seguenti: 

   1. Selezionare la prima colonna, OrderID.

   2. MAIUSC+clic sull'ultima colonna, Spedizioniere.

   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 su un'intestazione di colonna selezionata e scegliere Rimuovi altre 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.

1. In Anteprima dati selezionare l'icona della tabella () nell'angolo in alto a sinistra dell'anteprima.

2. Fare clic su Aggiungi colonna personalizzata.

3. Nella casella Formula colonna personalizzata della finestra di dialogo Colonna personalizzata immettere [Order_Details.PrezzoUnitario] * [Order_Details.Quantità].

4. Nella casella Nuovo nome colonna immettere Totale riga.

5. Selezionare OK.

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. In Anteprima dati fare clic con il pulsante destro del mouse sulla colonna DataOrdini e scegliere Trasforma > Anno.

2. Rinominare la colonna OrderDate in Year:

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

   2. Right-Click nella colonna DataOrdini selezionare Rinomina e immettere Anno.

Passaggio 6: Raggruppare le righe per ProductID e Year

1. In Anteprima dati selezionare Anno e Order_Details.ProductID.

2. Right-Click una delle intestazioni e seleziona 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. Selezionare OK.

   

Passaggio 7: Rinominare una query

Prima di importare i dati delle vendite in Excel, rinominare la query:

• Nella casella Nome del riquadro Impostazioni query immettere Total Sales.

Risultati: Query finale per l'attività 2

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

Riepilogo: Power Query passaggi creati in Attività 2 

Mentre 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 sulle formule Power Query, vedere Informazioni sulle formule Power Query.

Attività	Passaggio query	Formula
Connettersi a un feed OData	Origine	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Selezionare una tabella	Spostamento	= Source{[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	Aggiunta personalizzata	= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Passare a un nome più significativo, Lne Total	Colonne rinominate	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Trasformare la colonna OrderDate per visualizzare l'anno	Anno estratto	= Table.TransformColumns(#"Righe raggruppate",{{"Year", Date.Year, Int64.Type}})
Modifica in  nomi più significativi, DataOrdini e Anno	Colonne rinominate 1	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}})

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à si combinano le query Products e Total Sales usando una query Merge e l'operazione Espandi e quindi si carica la query Total Sales per Product nel modello di dati di Excel.

Passaggio 1: Integrare ProductID in una query Total Sales

1. Nella cartella di lavoro di Excel passare alla query Prodotti nella scheda del foglio di lavoro Prodotti .

2. Selezionare una cella nella query e quindi selezionare Query > Unisci.

3. Nella finestra di dialogo Unisci selezionare Prodotti come tabella principale e total sales come query secondaria o correlata da unire. Total Sales diventerà una nuova colonna strutturata con un'icona di espansione.

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. Seleziona Salva.

6. Selezionare 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 Impostare i livelli di privacy.

   

Risultato

L'operazione Merge crea una query. Il risultato della query contiene tutte le colonne della tabella primaria (Prodotti) e una singola colonna strutturata Tabella alla tabella correlata (Total Sales). Selezionare l'icona Espandi per aggiungere nuove colonne alla tabella principale dalla tabella secondaria o correlata.

Passaggio 2: Espandere una colonna unita

In questo passaggio si espande la colonna unita con il nome NewColumn per creare due nuove colonne nella query Products : Year e Total Sales.

1. In Anteprima dati selezionare Espandi icona () accanto a NewColumn.

2. Nell'elenco a discesa Espandi :

   1. Selezionare (Seleziona tutte le colonne) per cancellare tutte le colonne.

   2. Selezionare Anno e Totale vendite.

   3. Selezionare OK.

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

4. Per scoprire quali prodotti e in quali anni hanno ottenuto il volume di vendite più elevato, selezionare Ordinamento decrescente per totale vendite.

5. Rinominare la query in Total Sales per Product.

Risultato

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

In questo passaggio si carica una query in un modello di dati di Excel per creare un report connesso al risultato della query. Dopo aver caricato i dati nel modello di dati di Excel, è possibile usare Power Pivot per approfondire l'analisi dei dati.

1. Selezionare Home > Chiudi & Carica.

2. Nella finestra di dialogo Importa dati assicurarsi di selezionare Aggiungi questi dati al modello di dati. Per altre informazioni sull'uso di questa finestra di dialogo, selezionare il punto interrogativo (?).

Risultato

È disponibile una query Total Sales per Product che combina i dati del file Products.xlsx e del feed OData Northwind. Questa query viene applicata a un modello di Power Pivot. Inoltre, le modifiche apportate alla query modificano e aggiornano la tabella risultante nel modello di dati.

Riepilogo: Power Query passaggi creati in Attività 3

Mentre si eseguono le attività di query Merge 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 sulle formule Power Query, vedere Informazioni sulle formule Power Query.

Attività	Passaggio query	Formula
Integrare ProductID in una query Total Sales	Origine (origine dati per l'operazione Merge)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Espandere una colonna sottoposta a merge	Vendite totali espanse	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Rinominare due colonne	Colonne rinominate	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Ordinare le vendite totali in ordine crescente	Righe ordinate	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})