Power Query

Also called Get & Transform.
This is a built-in feature for creating data sources, making connections and shaping and transforming your data.
Not to be confused with Power BI which is a standalone business analytics application for creating interactive visualisations and reporting.
link - support.microsoft.com/en-us/office/unified-get-transform-experience-ad78befd-eb1c-4ea7-a55d-79d1d67cf9b3


Data Tab

alt text

Get Data - Drop-Down. Discover, connect and combine data from multiple sources. The drop-down includes the commands: From File, From Database, From Azure, From Online Services (New), From Other Sources, Combine Queries, Launch Query Editor (New), Data Source Settings and Query Options.
From Text/CSV - Import data from a text, comma-separated file or a text file with a space delimiter.
From Web - Import data from a web page.
From Table/Range - Create a new query linked to an Excel table or named range. If the selected range is not part of a table, it will be converted into a table automatically.
Recent Sources - Manage and connect to recent sources.
Existing Connections - Import data from common sources.


Refresh All - (Ctrl + Alt + F5). Button with Drop-Down. The button updates all the information in the active workbook coming in from external sources. The drop down includes the commands: Refresh, Refresh Status, Cancel Refresh and Connection Properties.
Queries & Connections - Displays the "Workbook Connections" dialog box. Data connections are links to data sources outside of this workbook.
Properties - This is only enabled when you select a cell that has been populated using an external connection.
Edit Links - Displays the "Edit Links" dialog box.


Four Categories

The functionality that is available in this group can be divided into four categories:
Connect - retrieve data that is saved locally, accessible via a service or in the cloud
Transform - shape the data based on your specific requirements
Combine - create a data model from multiple sources
Share - save and share your finished query


Updates

In Excel 2016 this functionality was built-in.
In Excel 2013 and 2010 this functionality was only available by installing the Data Explorer Addin add-in.
The Data tab changed significantly in the 2017 March Update. This page shows what it used to look like.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext