Windows 10: Excel Power Query

AlexandraJaner · Jun 29, 2021

I am using power query to combine excel files from a folder. There are line items with hyperlinks that are not transferring as hyperlinks when the query is run. Is there a way to correct this?

:)

Brink · Jun 29, 2021

April 2017 updates for Get & Transform in Excel 2016 and Power Query

Excel 2016 includes a powerful set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.Today, we are pleased to announce five new data transformation and connectivity features that have been requested by many customers.

These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved data connectivity and transformation features:

Support for the same file extensions in Text and CSV connectors.

ODBC and OLE DB connectors—support for Select Related Tables.

Enhanced Folder connector—support for “Combine” from the Data Preview dialog.

New Change Type Using Locale option in Column Type drop-down menu inside Query Editor.

New Insert Step After option in Steps pane inside Query Editor.

Support for the same file extensions in Text and CSV connectors

With this update, we revised the list of supported file extensions in From Text and From CSV connectors. Now, you can browse and select any text (*.txt), comma-separated value (*.csv) or formatted text space delimited (*.prn) file as the first step of the import flow for both connectors. Alternatively, you can switch to the All files (*.*) filter option and select to import data from any other unlisted file.

ODBC and OLE DB connectors—support for Select Related Tables

When using the ODBC and OLE DB connectors, we enabled the Select Related Tables button in the Navigator dialog. This option—already available for other relational data sources—allows users to easily select tables that are directly related to the set of already selected tables in the Navigator dialog.

Enhanced Folder connector—support for Combine Binaries from the Data Preview dialog

In the January 2017 update, we shipped a set of enhancements to the Combine Binaries experience. You can learn more about those enhancements in this article.

This month, we made it easier for customers to access the Combine Binaries feature by allowing them to combine multiple files directly from the folder Data Preview dialog within the Get Data flow, without having to go into the Query Editor.

Note that exposed multiple options (Combine and Combine & Load) allow customers to further refine their data before loading it into the worksheet or Data Model.

New Change Type Using Locale option in Column Type drop-down menu inside Query Editor

In the Query Editor, it is possible to see and modify column types by using the Column Type drop-down menu in the preview area.

With this release, we added the Change Type Using Locale option to this drop-down menu (previously available by right-clicking the column header and then selecting Change Type > Using Locale). This option allows you to specify the desired column type and locale to use for the conversion, which affects how text values are recognized and converted to other data types, such as dates, numbers, etc.

New Insert Step After option in the Steps pane inside Query Editor

With this update, we added a new context menu option in the Query Editor window to easily insert new steps in existing queries. The Insert Step After option lets users insert a new custom step right after the currently selected step, which can be the final step or any previous step within the query.

Learn more

Learn more about what’s new in Excel 2016.

Join our Excel community on Facebook and Twitter, and collaborate with others on Get & Transform Data at our new Tech Community forum.

Send us your ideas for other improvements on our Excel UserVoice page.

—The Excel team
Click to expand...

Source: April 2017 updates for Get Transform in Excel 2016 and the Power Query add-in - Office Blogs

Brink · Jun 29, 2021

April 2017 updates for Get & Transform in Excel 2016 and Power Query

Excel 2016 includes a powerful set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.Today, we are pleased to announce five new data transformation and connectivity features that have been requested by many customers.

These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved data connectivity and transformation features:

Support for the same file extensions in Text and CSV connectors.

ODBC and OLE DB connectors—support for Select Related Tables.

Enhanced Folder connector—support for “Combine” from the Data Preview dialog.

New Change Type Using Locale option in Column Type drop-down menu inside Query Editor.

New Insert Step After option in Steps pane inside Query Editor.

Support for the same file extensions in Text and CSV connectors

With this update, we revised the list of supported file extensions in From Text and From CSV connectors. Now, you can browse and select any text (*.txt), comma-separated value (*.csv) or formatted text space delimited (*.prn) file as the first step of the import flow for both connectors. Alternatively, you can switch to the All files (*.*) filter option and select to import data from any other unlisted file.

ODBC and OLE DB connectors—support for Select Related Tables

When using the ODBC and OLE DB connectors, we enabled the Select Related Tables button in the Navigator dialog. This option—already available for other relational data sources—allows users to easily select tables that are directly related to the set of already selected tables in the Navigator dialog.

Enhanced Folder connector—support for Combine Binaries from the Data Preview dialog

In the January 2017 update, we shipped a set of enhancements to the Combine Binaries experience. You can learn more about those enhancements in this article.

This month, we made it easier for customers to access the Combine Binaries feature by allowing them to combine multiple files directly from the folder Data Preview dialog within the Get Data flow, without having to go into the Query Editor.

Note that exposed multiple options (Combine and Combine & Load) allow customers to further refine their data before loading it into the worksheet or Data Model.

New Change Type Using Locale option in Column Type drop-down menu inside Query Editor

In the Query Editor, it is possible to see and modify column types by using the Column Type drop-down menu in the preview area.

With this release, we added the Change Type Using Locale option to this drop-down menu (previously available by right-clicking the column header and then selecting Change Type > Using Locale). This option allows you to specify the desired column type and locale to use for the conversion, which affects how text values are recognized and converted to other data types, such as dates, numbers, etc.

New Insert Step After option in the Steps pane inside Query Editor

With this update, we added a new context menu option in the Query Editor window to easily insert new steps in existing queries. The Insert Step After option lets users insert a new custom step right after the currently selected step, which can be the final step or any previous step within the query.

Learn more

Learn more about what’s new in Excel 2016.

Join our Excel community on Facebook and Twitter, and collaborate with others on Get & Transform Data at our new Tech Community forum.

Send us your ideas for other improvements on our Excel UserVoice page.

—The Excel team
Click to expand...

Source: April 2017 updates for Get Transform in Excel 2016 and the Power Query add-in - Office Blogs

Brink · Jun 29, 2021

October 2016 updates for Get & Transform in Excel 2016 & Power Query

Excel 2016 includes a powerful new set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce three new data transformation and connectivity features that have been requested by many customers.

These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved data connectivity and transformation features:

Query Parameters support.

Improved Web connector—web page previews.

Query Editor improvements—option to Merge/Append as new query.

Query Parameters support

With this update, users can now create and manage parameters for their queries within the Excel workbook. The new “Manage Parameters” dialog is available on the ribbon under the Home tab within the Query Editor.

The new dialog allows the users to create new parameters, give them a meaningful Name and Description, specify the information about the expected parameter type and values, default value and current value.

Once one or more parameters are available in the current workbook, users can reference those parameters in their queries via Query Editor. Referencing parameters is supported via the Data Source dialogs, Filter Rows, Keep Rows (top/bottom, etc.), Remove Rows (top/bottom, etc.), Replace Values, Add Conditional Columns dialog and more.

In addition, parameters can be loaded to the grid or to the Data Model just like any other query, allowing references from Excel formulas or DAX measures.

The in-depth tutorial on query parameters will be coming on the Excel blogs portal soon. Stay tuned.

Improved Web connector—web page previews

One of the most unique Get & Transform connectors is the Web connector. With the Web connector, users can easily import data from websites that has been formatted as an HTML table or even pull data from Web APIs.

When using the Web connector for “scrapping” data from HTML pages, a very common challenge is that the Navigator view, which is based on a list of tables, is not very helpful in identifying the desired tables. This is particularly hard when dealing with web pages that contain lots of tables, and in many cases, with not very representative table names.

With this update, we’re introducing a new mode in the Navigator dialog that allows users to preview tables on the web pages “in context” and select the desired tables by just clicking on them within the Web View preview. This results in a much more intuitive and seamless user experience for selecting tables from a web page.

To access this mode, click the Web View button at the top of the Navigator dialog. Users can also switch back to the classic data-centric view by selecting the Table View option.

Query Editor improvement—option to Merge/Append as new query

Within the Query Editor, users can easily merge (join) or append (union) multiple tables, allowing them to mash up data from multiple sources into a single table. The Merge/Append operations are on the ribbon under the Home tab inside Query Editor.

In previous versions of the Query Editor, Merge/Append operations were always applied as new steps within the current query. Starting with this update, users can decide whether to apply these operations as a new step in the current query (old behavior) or whether the output of the Merge/Append operation should be created as a new query (new behavior).

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

Learn more about what’s new in Excel 2016.

Join our Excel community on Facebook and Twitter, and collaborate with others on Get & Transform data at our new Tech Community forum.

Send us your ideas for other improvements on our Excel UserVoice page.

—The Excel team
Click to expand...

Source: October 2016 updates for Get Transform in Excel 2016 and the Power Query add-in - Office Blogs

Tip How to Check for Updates in Office 2016 for Windows

Windows 10: Excel Power Query

Excel Power Query

Excel Power Query

Excel Power Query

Excel Power Query - Similar Threads - Excel Power Query

Microsoft Excel Queries and ERP System Speed

Microsoft Excel Queries and ERP System Speed

Excel Query

power button and hibernate query

UAC Query Every Time I Open Excel

September 2016 updates: Get & Transform in Excel 2016 and Power Query

May 2017 updates for Get & Transform Excel 2016 & Power Query add-in

April 2017 updates for Get & Transform in Excel 2016 and Power Query

October 2016 updates for Get & Transform in Excel 2016 & Power Query