Excel 365 Part 21 – Get Data

This is an interface that allows a user to adjust the amount of data coming into Excel. It is an Add-In feature in older versions of Excel but is now located in the Data Ribbon Tab as the primary input feature. This means it will allow users to link to other data sources such as: Excel, CSV, XML, Text, SQL Server, Access, Oracle, MySQL, SharePoint List, Active Directory, websites across the enterprise, and Facebook. Once the data source is linked to the Query interface, you can reduce the amount of data to be saved in Excel or add/remove fields. Also, there are many customized techniques used to adjust the data including adjusting columns(fields), rows (records), splitting columns, combine columns, replace information, transpose the entire table, change the data type, and sort columns.

Video Series Part 21 - This is a comprehensive guide to learning Excel from the basics to advanced programming techniques. These videos are labeled in order beginning with Part 1, 2, 3, 4, etc. Each video can be downloaded and freely distributed to anyone desiring to learn Excel in great depth.
Higher Quality Video: www.elearnlogic.com/media/excel365-3-chmp4

- Click + Rumble if the video was helpful.
- Click Subscribe so you won’t miss out on any new videos.
- Click the Share button so you can help others expand their skills.

Each video provides a great overview of the concepts, but the courseware provides additional details for a better understanding. You will be able to follow along with the courseware while watching this video.

Courseware: Excel 365 - Formulas, Charts, And PivotTables
The courseware is available on Amazon at: https://www.amazon.com/dp/1660224934

Chapter 6 - PowerQuery (Get Data)
This is an interface that allows a user to adjust the amount of data coming into Excel. It is an Add-In feature in older versions of Excel but is now located in the Data Ribbon Tab as the primary input feature. This means it will allow users to link to other data sources such as: Excel, CSV, XML, Text, SQL Server, Access, Oracle, MySQL, SharePoint List, Active Directory, websites across the enterprise, and Facebook. Once the data source is linked to the Query interface, you can reduce the amount of data to be saved in Excel or add/remove fields. Also, there are many customized techniques used to adjust the data including adjusting columns(fields), rows (records), splitting columns, combine columns, replace information, transpose the entire table, change the data type, and sort columns. This data source in the Query Editor is used to filter, transform, or shape information. You can return to the Query Editor at any time to make additional adjustments as necessary. When finished, the resulting data can be saved to Excel, or if you are connected directly to a database data source, you can refresh the Query which will extract new or updated records that have been changed. Once the data is in Excel, it can be used as a Table structure, not a normal Excel Range.

Section 1: Get Data
This will Identify the data source, load the data, and adjust the data coming into Excel by applying filters to each column of data. When new data is added to the data source, the refresh button will bring in new data and apply the predefined filters to the incoming data. The following topics will be covered: PowerQuery Add-Ins, Get Data, Data Source, Load, Load To, Transform Data, Launch Query Editor, Filter, Left Pane, Center Pane, Applied Steps, and Close & Load.

Section 2: Home Ribbon Tab
This is the main menu to perform the most standard operations. The concepts will include: Close & Load, Refresh Preview, Properties, Advanced Editor, Manage, Choose Columns, Remove Columns, Keep Rows, Remove Rows, Sort, Split Column, By Group, Data Type, Use First Row As Headers, Replace Value, Manage Parameters, Merge Queries, Combine Append Queries, Data Source Settings, Change Source, Permissions, New Source, Recent Sources, and PowerQuery.

Section 3: Transform Ribbon Tab
The Transform Ribbon Tab changes or transforms data such as adding or removing columns, changing data types, splitting columns, and other data-driven tasks. The term Transform Data has to do with changing, filtering, adjusting, and shaping a data table to a new form or layout. The following buttons are in the Transform Ribbon Tab: Group By, Transpose, Reverse Rows, Count Rows, Data Type, Detect Data Type, Rename, Fill, Move, Convert To List, Split Columns, Format, Merge Columns, Extract, Parse, Statistics, Rounding, Information, Date, Time, Duration, Structured Column, Query Editor, and Transform Ribbon Tab.

Section 4: Add Column Ribbon Tab
This is primarily used to manipulate columns in a data source. Some of these options are available in other ribbons, however, these are the main column tools. The following topics will be covered: Column From Examples, Custom Column, Conditional Column, Index Column, and Duplicate Column.

Section 5: View Ribbon Tab
The View Ribbon Tab on the Query Editor interface is used to toggle whether certain panes or windows are displayed. It is also used to display the Advanced Editor. The following concepts will be covered: Query Settings, Formula Bar, Show Whitespace, Go To Column, Always Allow, Advanced Editor, Query Dependencies, and Query Editor.

This Courseware/Video is based on Excel 365 and documents command differences between Excel 2010, Excel 2013, Excel 2016, and Excel 2019.

If you have any questions, please contact: Jeff Hutchinson, Website: http://www.elearnlogic.com, Email: jeffhutch@elearnlogic.com