Power Query: Gt & Transform in Excel Following the January 2018 Update

I am hosting four videos here for my Excel blog. Please watch them if you are using Power Query aka Get & Transform in Office365 ... Excel 2016.

Four videos that discuss the four main changes to G&T that were just announced.

Your feedback will be highly valued and I look forward to sharing it.

Hop over to my Excel Blog to download the Excel file

Video 1 Bucket/Bin Ranges

Video 2 Positive/Negative Association

Video 3 Positive/Negative Association Explained in Full

Video 4 Creating Associated Lists

31st January 2018


Nobel Again

You have seen my long post on this subject, now read the shorter version in the Todmorden News ... Tod News

25th January 2018


You are only fooling yourself

The other day I was travelling and saw something that I found disheartening.

A very large man was travelling with someone else and they were sitting together and chatting. Then this morbidly obese man left his friend and came and sat next to me. He carried a bag with him that he opened and started to take food out of: greasy burgers and chips with lots of coleslaw. He ate this food as if he had hardly eaten for weeks. After he had gorged himself he closed his bag and clearly wanted to clean his hands before he returned to his friend. So he wandered to the toilet to get clean and then went back to sit next to his friend.

I think I felt sorry for this man: clearly he has an eating disorder of some sort that includes hiding his consumption from his friend. I hope he can find some peace that will allow him to stop this long, slow suicide that he's in the middle of.

22 Jan 2018


Analysis of Nobel Prizes

The place of Todmorden in the Annals of the Nobel Prize! as at the end of December 2017


My home town is Todmorden in West Yorkshire, England and throughout all of my childhood we were proud to say that Todmordian John Douglas Cockcroft had won the Nobel Prize for Physics in 1951: it was a joint award and he won it with Ernest Walton of Dungarvan, Ireland. We are told they were the first to have split the atom!

In 1973 another Todmordian, Geoffrey Wilkinson won the Chemistry prize along with Ernst Otto Fischer for their work on sandwich compounds: they were working independently of one another, it seems.

This put us in the stratosphere: which other town or city on the planet could boast TWO Nobel Prize Winners? Moreover, in spite of the 24 year age gap between them, Cockcroft and Wilkinson shared the same science teacher at Todmorden Grammar School.

This article sets out to answer a series of questions I have never seen answered before which includes, is Todmorden unique in respect of it Nobel Prize achievements? Is Todmorden at the top of any Nobel list? Has any other town or city produced more than two Nobel Prizes. Has any town of the size of Todmorden or less produced two, or more, Nobel Prize winners? … all low level stuff but I could not find anywhere THE source that would tell me everything I wanted to know.

Yes, the Official Web Site of the Nobel Prize, https://www.nobelprize.org, contains a massive amount of detail but it didn’t tell me, for example, if Todmorden is the smallest town to produce two Laureates and so on.

My File

I have produced an Excel file that contains all of the basic details of every Nobel Prize there has been: from 1901 to 2017. My list includes every Laureate, date of the award, dates of birth, dates of death, male or female and more.

Since I was trying to make Todmorden the centre of attention, I had said in a Todmorden based Facebook forum last week that I felt it is probably unique in producing two Laureates relative to its population size! I had long since given up the notion that Todmorden was top of the pile in all respects, of course; because we must expect the University cities of Oxford, Cambridge (UK and USA), Paris, Bonn … to outshine little old Todmorden!

By the way, Todmorden is not a University town so Cockcroft and Wilkinson belong to us by rght of birth: they are Tod Lads!

I went to various sources to find the populations of the cities for which there are two Nobel Laureates. I did not look for the populations of cities that had 1 or 3 or 4 or more Laureates since Todmorden is not competing with them by my reckoning!

You can download my file from the link at the end of this article (ths link will appear in the final version of this article) and for Excel warriors, you will find that I have used a wide range of techniques in there that includes various functions and formulas as well as Get & Transform/Power Query, including some programming in M.

How Many Prizes?

 Todmorden has claimed two prizes: how many other towns and cities have claimed the same or more or less? Examples

New York is top of the pile by a long way, London is third and as you can see, Todmorden is there with two Laureates. Overall, the number of cities by number of prizes is as follows:

Todmorden, then, is one of 44 cities around the world to have claimed two Nobel Prizes: one of just 91 cities to have claimed more than one Nobel Prize.

Ages of Laureates

John Cockcroft was 54 years old when he won his prize and Geoffrey Wilkinson was 52 years old. For a Physics Laureate, Cockcroft was almost two years younger than the average and for a Chemistry Laureate, Wilkinson was over six yearsyounger than the average.

By the way, the first Nobel Prizes were awarded in 1901, a relatively modern institution; but take a look at the dates of birth of the first Laureates, from 1901 and 1902:

The second ever Nobel Literature Laurate, Theodor Mommsen, was born on 30th November 1817: there was no other Laureate born before Theodor Mommsen!

By the same token, the first 20th century born Laureate did not appear until Frederic Joliot-Curie was awarded the Chemistry Prize in 1935. Frederic was born on 19th March 1900 and was part of the famous Curie family! For interest, here is the Curie family Nobel history:


To date, the youngest ever Laureate is Malala Yousafzai of Pakistan who was a mere 17 years old when she won the 2014 Peace Prize: Malala was born 180 years after Theodor Mommsen! Until then, the youngest ever Laureate had been the 25 year old Australian William Lawrence Bragg who won the Physics Prize in 1915. The oldest prize winner to date is Russian born Leonid Hurwicz, who was 90 years old when he was awarded the 2007 Economics Prize.

Sex of Prize Winners

Up until the end of 2017, women have won just 49 Nobel Prizes, men have scooped the other 847: almost 30 prizes have been awarded to Organisations, so they are gender neutral!

Marie Curie’s award in 1903 was the first by a woman and in the first 20 years in which the Prizes were awarded, only four went to women. In the 20 years to the end of 2017, women have been awarded 21 Prizes.

I will attempt no answer to suggest why more women have not earned Nobel Prizes and I have to say that over the last 20 or so years, women have been actively lobbying for more women Laureates. Since they clearly cannot create scientific achievement out of thin air, the majority of prizes for women have been the Peace and Literature Prizes with a further 12, Physiology or Medicine, Prizes having been awarded to women!

Please note, the relatively small number of Prizes for Economics reflects the fact that the first Economics Prizes were not awarded until 1969. Moreover, the title of this Prize is, in full: The Sveriges Riksbank Prize in Economic Sciences in Memory of Alfred Nobel.

Populations and the Final Answer

For me, this is the big question: person for person, is a Todmorden Nobel Prize worth more than anywhere else on the planet? In other words, is Todmorden the smallest town in the world to have been awarded two Nobel Prizes?

Please note, I have not gone back to the dates of the award of the Prizes to find the populations of the cities concerned: in some cases that would be impossible anyway. What I have done is to take the latest figure I can find on the internet as at 8th January 2018 for all of the cities of interest.

Well, a two Prize city has a mean population of over 960,000 and a median of almost 388,000 inhabitants. Yay! Tiny Todmorden has around 15,500 inhabitants. The largest two Prize city is Mexico City with 8.9 million people, Halifax, 12 miles from Todmorden also has two Prize winners and has a population of 88,000 or thereabouts.

So, the smallest two Prize city is … Rendcombe in Gloucestershire with a population of just 354 people. More than that, their two Prizes came from one man, Frederick Sanger who won the Chemistry Prize outright in 1958 and outright again in 1980. Hats off to Frederick!


There you are: good news, bad news! Todmorden is in the elite Nobel Prize World by having two Nobel Prize winners in its history. It is the smallest town to have two Prize winners. Alas, it is not the smallest village! Let’s give due credit to Frederick Sanger and Rendcombe (or Rendcomb) for not only winning two Prizes but winning them both outright: a very rare achievement.

Sanger helped to answer another of the questions that was raised on the Facebook page I mentioned earlier and that is: is Todmorden Grammar the only school to have produced two Nobel Prizes? Again, no! However, let’s find a winning angle for Todmorden: Sanger won two prizes but he is only one Laureate, albeit Laureate and bar! So, for having two Laureates from the same school, Tod is Tops!


Finally, this article took me several days both to research and for me to carry out the analysis in my Excel file: I humbly apologise if there are any errors here and if you do find something amiss, please let me know and I will correct it! I have made very few changes to the basic data but I have had to make a very small number of minor tweaks to place names/locations to prevent misrepresentation.

Acknowledgements I am grateful to the Nobel Prize organisation for making their database freely available and for the various sites that provided me with population and other data: in that regard, mainly www.wikipedia.com

Duncan Williamson
9th January 2018

Excel file for download: the link will appear here in the final version of this article



I have loved having a garden for as long as I can remember and I first owned my own garden in 1983. It was tiny and I went from complete tripe to overwhelmed by one kind of flower to mediocre. My garden in Malawi was fantastic: real credit to our garden boy and, of course, my estate management skills. Ahem! I am taking back control of my garden here, now. We inherited some trees and bushes and they are still here. I laid a lawn front and sides of the house and whilst they don't sell what I call grass here, it looks like a lawn and I mow it from time to time to keep it neat. I love kitchen gardens and have now taken control of that aspect too. Mrs W is prone to planting a hundred seeds of one plant that then grows and overwhelms us with a harvest we cannot appreciate: she gets things to grow, at least. I am taking a more measured approach now and have planted a lot fewer seeds and bulbs but will plant again in a few weeks' time to get a proper flow of plants going. Let's see how it all works out. I will report back from time to time. DW

Love Actually ... well, no, actually

I bought the DVD of Love Actually at least twice and I watch it just about every year. So, it's time to watch it again. Except, of course, Windows 10 in all its fantastic glory will not allow me to play it. Who knows what the problem is? Genuine DVD. Played several times already. Not scratched or damaged in any way. Very frustrating DW


Trip to Khao Kho

We live in a rice farming area which is, not surprisingly, very flat. No hills, hardly a slope in sight. So far a weekend away I insisted that we went somewhere hilly. Goodness, did we find some hills. And so VERY steep roads! We can to Khao Kho in central Thailand. It's a very nice area with lots of twisty roads and some scary hill climbs. My legs were rewarded with the resistance I was looking for and there are some interesting things to do here and interesting things to see. By the way, even on the top of a couple of hills, on sloping ground, we saw rice being grown! DW


The Pushchair NOT the Baby

Waiting to check in for my flight from Istanbul to Doha when a man carrying a very young baby walked in front of me on his way to another counter. A short conversation that I could not hear took place, after which the check in clerk laughed heartily as she said for all the world to hear, No, not to check in the baby, check in the baby stroller!! The man went past me again as he took his baby away. He came back within a few minutes minus baby, plus pushchair! Some entertainment at least.


Scooby Doo

It's hardly the most important thing I ever thought about but I could not abide that television programme, Scooby Doo. It really got on my nerves. In my inbox today I received my usual OED word of the day message to find the word Scooby ... here is the entry: scooby, n. [‘not to have a scooby: = not to have a clue at clue n. 2e.’] Pronunciation: Brit. /ˈskuːbi/, U.S. /ˈskubi/, Scottish /ˈskubɪ/ Forms: 19– scoobie, 19– scooby. Origin: Formed within English, by clipping or shortening. Etymon: proper name Scooby. Etymology:Short for Scooby Doo, the name of a cartoon dog which features in several U.S. television series and films (which typically include the name of the dog in the title), as rhyming slang for clue n. The fuller form scooby doo is also sometimes found. colloq. (chiefly Sc.). not to have a scooby: = not to have a clue at clue n. 2e. 1993 Herald (Glasgow) 14 May 16 Your lawyer telling youse that he husnae a scooby and youse can jist take a wee tirravie tae yersel. 1999 C. Dolan Ascension Day(2000) vi. 120 Mum, trying to be businesslike, quizzed Morag about blood counts and bone marrow suppression and other such matters about which of course she didn't have a scooby. 2006 Daily Record (Glasgow)(Nexis) 12 May 33 Isn't research meant to ask questions we haven't a scoobie about? May the saints preserve us!! DW


RIP Liam Coughlan

I saw a question on quora.com today that piqued my interest so I answered it: it concerned the British view of Irish people. Since I have worked and admired several Irish people over the years, I responded to say so. Following on from there I thought I would try to find the Irishmen I worked with by way of a google search and was saddened by what I found. I read a story online about my old Irish friend Liam Coughlan. We met and worked together in Yeravan then Tblisi and then as I was posted to Bishkek, Liam was posted to Tashkent: we both still worked for the same organisation in the same project but we were countries apart. We met briefly in Croatia when Liam invited me to run a one weekcourse for him there a while after we had finished our Central Asian gig. We lost touch after that! Liam was one of the smartest men I have ever known: highly qualified in his field; holding high level positions in a variety of organisations. Liam was generous, friendly, open, talkative, informative, supportive, helpful, down to earth, very well read on Ireland and Irish history and politics. Liam was also politically very astute. Liam also had a fantastic sense of humour and any time spent with him was bound to entail his endless blarney and endless anecdotes. He knew or had met countless people: honest johns and downright rogues and he had stories about them all. Liam was educated by the Christian Brothers in Ireland and what they did to him and to others is beyond the pale. Stories of brutality, sexual activity, paedophilia and possibly murder seemed to haunt every waking hour of anyone who was in the throes of anything to do with that outfit. I was transfixed by his stories but never doubted them. Strories in newspapers, books and online match what Liam told me. He regaled me with stories of when he stood for Parliament in Ireland and how he realised how stupid he might have been to try. He was certainly clever and honest enough but maybe a little idealistic. In any case, at or around that time he gave a lift on the back of his motorbike to Charles Haughey, now the late and unlamented Taoiseach whom Liam branded a cheat, liar, thieving arse. At the time I knew Liam I reviewed every book I read on my web site and he not only read my reviews but he commented on them: normally constructive and supportive … apart from the book I reviewed on Haughey. Let me confess that Liam lent me the book and I didn’t read all of it so my review was a little short of proper insight. I got an email from Liam setting me straight and I never admitted my shortcomings but I published Liam’s correction without hesitation! Of the two of us, I was the qualified teacher but Liam was by far the better educator. Because of his intelligence and diligence, he took subjects apart and rebuilt them. He had a learner’s insight and a teacher’s gift and his students were definitely the better for it. He had two women ACCA students in Tblisi and they both sailed through every exam because of Liam and they became qualified accountants in double quick time. If ever a beggar or a hawker came anywhere near Liam or the people he was with, he would be the first or the only one to buy what they were selling or to give them something to eat or just to hand over a few coins or notes. I have known no one else like that. In terms of the question on quora.com, Liam was very clear about that: he liked and respected British people and if anyone tried to say there was hatred between the two nations, he would easily strip out the rumour and gossip and explain who the haters were, where they were, what they wanted and how few of them there were! He could have been an ambassador for Ireland. The story I read about Liam online this morning said that he had died from natural causes in Austria, as testified by the Austrian police: aged 51. I knew his partner had had a baby shortly after we went our separate ways but it seems there was another one after that. The crux of the story I read today concerned the woman who passed herself off as Liam’s first wife who claimed no knowledge of the second wife. Let it be known, I knew about both women and I met the second one several times as she was in Tblisi and elsewhere with him. I never met the first wife but I heard a lot about her and their son. Liam never hid from his responsibilites and I imagine he was a fantastic father but he has died tragiccally very young and I wish his children well. One of the last times we met face to face was in Ireland. At the end of our Central Asian work I said I was looking for somewhere to go on holiday and he suggested Ireland so that’s where I went. From South Wales to Waterford by car ferry and then a week doing a grand tour of Ireland, ending up in Belfast. Liam met me in Waterford and we had a jar or two of Porter there. Grand craic was had during that week and Ireland is a place well worth visiting: Waterford to Cobh, Limerick, Kerry, Knock (Liam had a story about that, too!), Galway and across to Belfast. Excellent drivers in Ireland I have to say: very corteous. I noted the speed signs on the roads as I went from the ferry to the hotel where I met Liam and I asked him, are those signs in miles per hour or kilometers …he replied, you decide! Typical Irish, typical Liam. I am sorry I lost touch but these things happen and I am very sorry to hear that he has died: by the way, no surprise as he smoked like a chimney and eschewed just about every form of exercise known to man! I shoud say, I don’t know what killed him but he did smoke a lot and I know he was treated from time to time for possible skin cancer given the type of skin he had. Nevertheless, ave atque vale Liam. It really was a pleasure and a privilege to have known you. Duncan October 2017


Recipe time: veggie sandwich

Lightly fry the following: Medium sized onion, sliced Clove of garlic crushed and chopped Stick of celery chopped Button mushrooms sliced Fry the onions and garlic for a minute Add the celery for two minutes Add the mushrooms with some ground black pepper, a pinch of salt and 6 or 7 splashes of Worcestershire sauce and keep cooking for another two minutes Meanwhile Toast two slices of your favourite bread and after they have cooled for two minutes spread hummus on both sides of each slice Pile half of your onion mix onto the first slice of bread. Put the second slice I top of that. Now pile the rest of the onion mix on top of the second slice Serve with a salad of your choice if you wish but I couldn't wait! I make my own hummus but shop bought is normally just as good. DW 11th September 2017


American Food

I worked with Mrs W over the last few days to write an introductory piee on American Food: here it is! The USA is the richest country in the world and it has the biggest, most expensive and often the best of everything. There are about 300 million people living in the USA and apart from Native American Indans, they have come from all parts of the world. As these people arrived from Europe, Africa, Asia, South America and elsewhere, they brought their ideas, cultures and cuisines with them. We know about Little Italy and Chinatown in New York, there is the Latin Quarter in New Orleans. All over the country, the food comes from here, there and everywhere. • Jerky • Dutch Baby Pancake • Jersey Breakfast • Shrimp and Grits • Hoppel Poppel • Scrapple • Tex Mex And a lot more A lot of these imported dishes are good quality, nutritious dishes that everyone can eat but there is downside to food from the USA: fast food. Since the 1940s the USA has been the leader in creating food and restaurants that cater to speed and greed. People who either cannot cook or who cannot be bothered to cook. Food portions that can be carried away in buckets, not just on plates. Beefburgers made to be the size of a man’s head. Deep fried food. Food with lots of fat and sugar and carbohydrates. Burgers, fried chicken and pizzas. At least two generations of Americans have been brought up on fast food and the result is that more than half of all Americans are not just overweight, not just fat but they are obese … very fat. No surprise when a single meal at MacDonald’s can contain 1,600 calories and many grammes of fat as well. As countries develop and get richer, the more its people get fatter: it’s time to stop and think. We admire the development in the USA but we do not need its fast food and its obesity. DW 26th August 2017


Leek and Potato Soup

Last night I made the best leek and potato soup I have ever made and the secret is the potato! Serves Two Ingredients small onion, sliced one medium sized leek, washed and sliced small potato, washed and chopped ... peeling is optional, I did not peel it half a vegetable stock cube or add real stock or water ... about 15 fluid ounces/400 millilitres dessertspoon of vegetable oil Method Heat the oil in a pan ...medium heat ... and add the onions ... sweat them for a minute Add the leek ... sweat them for a minute Add the potato and continue to sweat all of the veg for another two minutes Add the stock cube and stir it in Now add the liquid/stock Bring to the boil Simmer until the potatoes are soft ... maximum 10 minutes should be enough At this point, take the soup off the heat, pour it into a blender and blend it thoroughly If it is too thick for you, simply add more water or stock It is ready now. Add any adornments you like such as a sprig of parsley or coriander leaves ... as you wish The potato secret? Don't add too much otherwise it dominates the taste DW


The Cost of Light!

Flick a switch and the light comes on. Have you ever thought about the cost of light throughout history? I found a table of the cost of lighting per million lumen hours in the UK in British Pounds for the period 1301 to 2006. In 1301 The estimated cost of lighting for one million lumen hours was £33,042.9 whereas in 2006, the cost was ƒ2.89 per one million lumen hours. Oddly, the cost in 1301 had rocketed to £40,820.58. Take a look for yourself at this page from DER SPIEGEL/Statista, where you can read: "One hour of light (referred to as the quantity of light shed by a 100 watt bulb in one hour) cost 3,200 times as much in 1800 in England as it does today, amounting to 130 Euros back then (or a little more than 150 dollars). In 1900, it still cost 4 euros (close to 5 dollars). In the year 2000, we arrived at a cost of 4 euro cents (5 US cents)." The following chart shows how much work we have to do to be able to switch the lights on: one second now, 400 hours in 1750 BC! Infographic: The Cost of Light Through the Ages | Statista You will find more statistics at Statista Fascinating stuff! DW


Air Malawi: welcome back

I last flew on an Air Malawi aeroplane in the middle of 1993 as I finished a five year posting to the University of Malawi in Blantyre. They had bought two new planes shortly before I left the country but I flew on neither of them. I flew on the BAC111 and the turbo prop plane that used to frighten me to death as it bounced around the skies! Today I will fly from Lusaka in Zambia to Dar es Salaam in Tanzania via Lilongwe in Malawi on Malawian Airlines. I am looking forward to seeing how Malawi Airlines has changed things in the last 23 years and I can see from the web that they fly some smart planes now. My plane today will be the Dash 8 Q400, for both legs of the journey ... Dash 8 Q400 And here, a Boeing 737-700: Boeing 737-700 I have seen a Boeing 747 in Air Malawi livery but I don't see that plane on their web site now where they say they have just the two planes you see above. See you there and muli bwanji in advance! DW