In questa esercitazione usare l'editor di query di Power Query per importare dati da un file di Excel locale che contiene informazioni sui prodotti e da un feed OData che contiene informazioni sull'ordine dei prodotti. Esegui i passaggi di trasformazione e aggregazione e combina i dati di entrambe le origini per creare un report sulle vendite totali per prodotto e anno .
Per completare questa esercitazione, è necessaria la cartella di lavoro Prodotti . Nella finestra di dialogo Salva con nome assegnare al file il nome Products and Orders.xlsx.
Attività 1: Importare i prodotti in una cartella di lavoro di Excel
In questa attività i prodotti vengono importati dal file Prodotti e Orders.xlsx (scaricato e rinominato nella sezione precedente) in una cartella di lavoro di Excel. Si alzano quindi di livello 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
- Creare una cartella di lavoro di Excel.
- Selezionare Dati>:Recupera dati>da fileda una> cartella di lavoro.
- Nella finestra di dialogo Importa dati cercare e individuare il fileProducts.xlsx scaricato e quindi selezionare Apri.
- Nel riquadro Navigatore fare doppio clic sulla tabella Prodotti . Viene visualizzato l'editor di Power Query.
Passaggio 2: Esaminare i passaggi della query
Per impostazione predefinita, Power Query aggiunge automaticamente diversi passaggi per facilitare l'utente. Per altre informazioni, esaminare ogni passaggio in Passaggi applicati nel riquadro Impostazioni query .
- Fare clic con il pulsante destro del mouse sul passaggio Origine e selezionare Modifica impostazioni. Questo passaggio è stato creato durante l'importazione della cartella di lavoro.
- Fare clic con il pulsante destro del mouse sul passaggio di navigazione e selezionare Modifica impostazioni. Questo passaggio è stato creato quando è stata selezionata la tabella nella finestra di dialogo di spostamento .
- Fare clic con il pulsante destro del mouse sul passaggio Tipo modificato e selezionare 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 altre colonne per visualizzare solo le colonne di interesse
In questo passaggio vengono rimosse tutte le colonne tranne ProductID, ProductName, CategoryID e QuantityPerUnit.
- In Anteprima dati selezionare le colonne IDProdotto, NomeProdotto, IdCategoriaCategoria e QuantitàPerUnità (usare CTRL+clic o MAIUSC+clic).
- Selezionare Rimuovi colonne>,Rimuovi altre colonne.
Passaggio 4: caricare la query sui prodotti
In questo passaggio la query sui prodotti viene caricata in un foglio di lavoro di Excel.
- Seleziona Home,>chiudi & carica. La query viene visualizzata in un nuovo foglio di lavoro di Excel.
Riepilogo: passaggi di Power Query creati nell'attività 1
Mentre si eseguono attività di query in Power Query, vengono creati passaggi della query e vengono 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 di Power Query, vedere la documentazione di Power Query.
| 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 delle colonne | 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"}) |
Attività 2: Importare i dati degli ordini da un feed OData
In questa attività si importano i dati nella cartella di lavoro di Excel dal feed OData di esempio Northwind all'indirizzo http://services.odata.org/Northwind/Northwind.svc, espandere la tabella Order_Details, rimuovere colonne, calcolare il totale delle righe, trasformare un valore DataOrdine, raggruppare le righe per IDProdotto e Anno, rinominare la query e disabilitare il download delle query nella cartella di lavoro di Excel.
Passaggio 1: Connettersi a un feed OData
- Selezione dei dati>Recupera dati>da altre origini>dal feed OData.
- Nella finestra di dialogo Feed OData immettere l'URL per il feed OData Northwind.
- Selezionare OK.
- Nel riquadro Navigatore , fare doppio clic sulla tabella Ordini .
Passaggio 2: Espandere una tabella Order_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 unite in righe con la tabella primaria (Ordini).
In Power Query, una colonna contenente una tabella correlata ha il valore Record o Table nella cella. Queste colonne sono denominate colonne strutturate. Record indica un singolo record correlato e rappresenta una relazione uno-a-uno con i dati correnti o la tabella primaria. Tabella indica una tabella correlata e rappresenta una relazione uno-a-molti con la tabella corrente o primaria. 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 di SQL Server.
Dopo aver espanso la tabella Order_Details , nella tabella Ordini vengono aggiunte tre nuove colonne e altre righe, una per ogni riga della tabella annidata o correlata.
In Anteprima dati scorrere orizzontalmente fino alla colonna Order_Details .
Nella colonna Order_Details selezionare l'icona di espansione (
).Nell'elenco a discesa Espandi:
Selezionare (Seleziona tutte le colonne) per cancellare tutte le colonne.
Selezionare IDProdotto, PrezzoUnitario e Quantità.
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 oggetto. Per altre informazioni su come eseguire operazioni di aggregazione, vedere Aggregare dati da una colonna (Power Query).
Passaggio 3: Rimuovere altre colonne per visualizzare solo le colonne di interesse
In questo passaggio vengono rimosse tutte le colonne tranne le colonne DataOrdine, IDProdotto, PrezzoUnitario e Quantità .
In Anteprima dati selezionare le colonne seguenti:
- Selezionare la prima colonna, IDOrdine.
- MAIUSC+clic sull'ultima colonna, Mittente.
- Selezionare le colonne OrderDate, Order_Details.ProductID, Order_Details.UnitPrice e Order_Details.Quantity premendo CTRL+clic.
Fare clic con il pulsante destro del mouse sull'intestazione di una colonna selezionata e scegliere Rimuovi altre colonne.
Passaggio 4: Calcolare il totale delle righe per ogni riga Order_Details
In questo passaggio verrà creata una Colonna personalizzata per calcolare il totale della riga per ogni riga di Order_Details.
- In Anteprima dati selezionare l'icona della tabella (
) nell'angolo superiore sinistro dell'anteprima. - Selezionare Aggiungi colonna personalizzata.
- Nella casella Formula colonna personalizzata della finestra di dialogo Colonna personalizzata immettere [Order_Details.PrezzoUnitario] * [Order_Details.Quantità].
- Nella casella Nome nuova colonna immettere Totale riga.
- Selezionare OK.
Passaggio 5: Trasformare una colonna Data ordine anno
In questo passaggio si procederà alla conversione della colonna OrderDate per visualizzare l'anno della data dell'ordine.
In Anteprima dati fare clic con il pulsante destro del mouse sulla colonna DataOrdine e selezionare Trasforma>anno.
Rinominare la colonna OrderDate in Year:
- Fare doppio clic sulla colonna DataOrdine e immettere Year or
- Fare clic con il pulsante destro del mouse sulla colonna DataOrdine , selezionare Rinomina e immettere Anno.
Passaggio 6: Raggruppare le righe in base a IDProdotto e anno
In Anteprima dati selezionare Year e Order_Details.ProductID.
Fare clic con il pulsante destro del mouse su una delle intestazioni e selezionare Raggruppa per.
Nella finestra di dialogo Raggruppa per:
- Nella casella di testo Nuovo nome di colonna digitare Total Sales.
- Nell'elenco a discesa Operazione selezionare Somma.
- Nell'elenco a discesa Colonna selezionare Line Total.
Selezionare OK.
Passaggio 7: Rinominare una query
Prima di importare i dati di vendita in Excel, rinominare la query:
- Nella casella Nome del riquadro Impostazioni query immettere Vendite totali.
Risultati: Query finale per l'attività 2
Dopo aver eseguito ogni passaggio, è presente una query Total Sales sul feed OData Northwind.
Riepilogo: passaggi di Power Query creati nell'attività 2
Mentre si eseguono attività di query in Power Query, vengono creati passaggi della query e vengono 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 di Power Query, vedere la documentazione di 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 di Custom |
= 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(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}}) |
| Modifica in nomi più significativi, OrderDate e Year |
Colonne 1 rinominate |
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}}) |
Attività 3: Combinare le query Products e Total Sales
Power Query consente di combinare più query unendole o aggiungendole. È possibile eseguire l'operazione di unione su qualsiasi query di Power Query con una forma tabellare, indipendentemente dall'origine dati. Per altre informazioni sulla combinazione di origini dati, vedere Combinare più query (Power Query).
In questa attività è possibile combinare le query Prodotti e Vendite totali usando un'operazione di unione ed espansione , quindi caricare la query Vendite totali per prodotto nel modello di dati di Excel.
Passaggio 1: Unire ProductID in una query Total Sales
Nella cartella di lavoro di Excel passare alla query Prodotti nella scheda Foglio di lavoro Prodotti .
Selezionare una cella nella query e quindi selezionare Unione query>.
Nella finestra di dialogo Unisci selezionare Prodotti come tabella primaria e Vendite totali come query secondaria o correlata da unire. Total Sales diventa una nuova colonna strutturata con un'icona di espansione.
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.
Nella finestra di dialogo Livelli di privacy:
- Selezionare Organizzativo come livello di isolamento della privacy per entrambe le origini dati.
- Selezionare Salva.
Selezionare OK.
Nota
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 (Power Query).
Risultato
L'operazione Merge crea una query. Il risultato della query contiene tutte le colonne della tabella primaria (Prodotti) e una singola colonna strutturata di tabella alla tabella correlata (Vendite totali). Selezionare l'icona Espandi per aggiungere nuove colonne alla tabella primaria dalla tabella secondaria o correlata.
Passaggio 2: Espandere una colonna unita
In questo passaggio espandere la colonna unita con il nome NewColumn per creare due nuove colonne nella query Prodotti : Anno e Vendite totali.
In Anteprima dati selezionare l'icona Espandi (
) accanto a NewColumn.Nell'elenco a discesa Espandi :
- Selezionare (Seleziona tutte le colonne) per cancellare tutte le colonne.
- Selezionare l'anno e le vendite totali.
- Selezionare OK.
Assegnare a queste due colonne i nomi Year e Total Sales.
Per scoprire quali prodotti e in quali anni hanno ottenuto il maggior volume di vendite, selezionare Ordina in ordine decrescente per vendite totali.
Rinominare la query in Total Sales per Product.
Risultato
Passaggio 3: Caricare una query sulle vendite totali per prodotto in un modello di dati di Excel
In questo passaggio si carica una query in un modello di dati di Excel, in modo da poter creare un report connesso al risultato della query. Dopo aver caricato i dati nel modello di dati di Excel, è possibile usare PowerPivot per approfondire l'analisi dei dati.
- Seleziona Home,>chiudi & carica.
- 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
Si dispone di 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: passaggi di Power Query creati nell'attività 3
Quando si eseguono attività di query di unione 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 di Power Query, vedere la documentazione di 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 | Aumento delle vendite totali | = 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 il totale Vendite in ordine crescente | Righe ordinate | = Tabella.Ordina(#"Colonne rinominate",{{"Vendite totali", Order.Crescenting}}) |