SOLVED

January 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

Microsoft

Hi friends,

 

We have recently announced the January 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in.

 

In this update, we have released the following features:

  • New OLE DB connector.
  • Enhanced “Combine Binaries” experience when importing from any folder.
  • Maximize/Restore buttons in the Navigator and Query Dependencies dialogs.
  • Support for percentage data type.
  • Improved “Function Authoring” experience.
  • Improved performance for OData connector.

Please refer to this blog post for more details:
https://blogs.office.com/2017/02/08/january-2017-updates-for-get-transform-in-excel-2016-and-the-pow...

 

Guy

- Excel Team

7 Replies

They say :

 

"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."

 

What if I have Excel 2016 not being part of Office 365 ?

 

Thanks

 

I have been working on the G&T January 2018 updates and they are really good. 

Creating buckets or bin ranges such as 0 to 50 ... 50 to 100 is a real time saver and it works and changes as the data change.

Creating a list such as city and country allows me to match the two but the added advantage, of course, is that I can create a full list and use, say, just half of the list or even just one item at any time and G&T will correctly assign the correct country. Clearly there are MANY possible uses for this feature.

This means that I created an Excel Table with just two columns, City and Values and from there, using G&T, generated a table with six useful columns and from there three excellent pivot tables ...

I faced a problem with the negative and positive query, though:

I created a number series as below

-46 -41 -29 -28 -21 -19 -14 -13 -8 -4 -2 0 90 19 19 32

and it created the query rules 

if values >=9 positive

if values >=0 zero

otherwise negative

This is correct for that series but when I entered a new data set

-50 -43 -34 -33 -26 -22 -19 -17 -12 -8 -7 0 5 15 18 27

It still uses the rule generated in the first instance and therefore its assignment of 5 is wrong in the new series and you can see why!

I do know how to correct this problem and changed the rule to the more general

if values >0 positive

if values >=0 zero

otherwise negative

And I like that. Moreover, as an accountant, I change positive etc for favourable, adverse and "-" ... as if the differences were variances 

 

I use G&T a lot and obviously it is getting better and better so I would value your views on what I have found with January's update so far.

 

Best wishes

 

Duncan

Hi Duncan,

 

The problem that you described with the formula generated for negative numbers series is kind of by design. The Column by Examples generates the formula during authoring time based on the current data set. If you change the data set later - the formula does not change. You will need to recreate the formula by running Column by Examples again, or by changing the M statement manually (as you did).

 

Guy

- Excel Team

In the old tool for importing data from a CSV-file it was easy to select a column with a JMD date in it (eg totday: 180208) and convert this into an actual date in Excel. I cannot find the way how to do this in the new interface. 

Hi,

 

Power Query works in a slightly different way and uses locale for date conversions. Take a look at the following article for more details:

https://support.office.com/en-us/article/internationalization-power-query-d42b9390-1fff-413f-8120-d7...

 

Hope it helps.

 

Guy.

- Excel Team

1 best response