Feb 09 2017
03:31 AM
- last edited on
Jul 12 2019
10:41 AM
by
TechCommunityAP
Feb 09 2017
03:31 AM
- last edited on
Jul 12 2019
10:41 AM
by
TechCommunityAP
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:
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
Feb 20 2017 12:47 PM
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
Feb 21 2017 04:35 AM
SolutionDaniel, perhaps this discussion will help https://social.technet.microsoft.com/Forums/en-US/db5efab1-be7b-4541-be73-b24682796d6c/how-to-get-th...
Feb 21 2017 05:02 AM
Thanks Sergei. False question.
Jan 30 2018 03:51 PM
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
Feb 04 2018 08:18 AM
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
Feb 08 2018 08:45 AM
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.
Feb 13 2018 07:34 AM
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:
Hope it helps.
Guy.
- Excel Team
Feb 21 2017 04:35 AM
SolutionDaniel, perhaps this discussion will help https://social.technet.microsoft.com/Forums/en-US/db5efab1-be7b-4541-be73-b24682796d6c/how-to-get-th...