Excel 2016 “Get and Transform” and the “M” language for advanced Business Analysis

September 3, 2016 david.taylor

Exploiting business opportunities ahead of the competition relies upon strong analytical tools which are agile yet are also powerful. Analysts within the business need such tools to be useful out of the box but will also need them to support advanced techniques when necessary.

Spreadsheets have always been the tool of choice for financial analysis and financial modelling. What about for data analysis and data modelling? Legacy spreadsheets had lookup functions that helped with data, non-numeric analysis, but as mentioned in an earlier post the fit was clumsy and error prone. Modern Excel, 2010 and beyond, include data tools either built right in or as add-ins from Microsoft. Power Query is a case in point. It’s built into Excel 2016 under the Data tab as “Get and Transform”.

Power Query uses a language informally called “M”. In this blog post with just a few lines of code a table of sales records is put through a “Get and Transform” process. The example here will add a cumulative running total automatically using the M language.

2016-09-03

The Excel Query Editor contains three items:

  1. A query to extract the Sales data from a source and to add the index column
  2. A function called YTD() which returns a summation of the sales value for the given index plus all earlier periods; and
  3. A query that references the first query but adds the column “Running Total” which transforms the table into a richer data set to be loaded into Excel.
 Now for a look at the code for the YTD() function:
2016-09-03 (1)How it works is via recursion.
For example to calculate a result for the index value of 9, the function call is made “=YTD(9)” in another query. It will evaluate the sum of 285 plus 3714 automatically. Here’s the advanced part; It gets 3714 from @YTD(8) which it calls itself from within the “else” calculation inside the function itself. That inner result is evaluated as sum 280 plus 3434. Similarly it gets 3434 from YTD(7) which is evaluated as sum 368 plus 3066 and so on….until it reaches YTD(0) which is a terminal value detected by the “If index=0” then 497

The way that M handles recursion is that a function can call itself from inside that same function provided that it uses the @ symbol when calling itself. so @YTD(Index-1) is used each time that Index is above the value 0.

Here is a link to the above solution in a workbook.
https://1drv.ms/x/s!Agc3uI9a_2aOiUhhXmb5HS7G528j

At the heart of Excel’s “Get and Transform” or Power Query capability in the case of Power BI, the M language provides advanced transformational capability for the savvy analyst. The M language is not procedural but is a declarative and functional language which does not require the analyst to spell out every step; in contrast to VBA which does. So looping through data record by record is handled using elegantly concise function blocks, leaving time to instead analyse the insights and get ahead of your competition.

6,945 Comments on “Excel 2016 “Get and Transform” and the “M” language for advanced Business Analysis

  1. Amazing blog! Do you have any recommendations for aspiring writers?
    I’m plannikng to start my own blog soon but I’m a little
    lost on everything. Would you propose starting with a free platform like
    Wordpress or ggo for a paid option? There are so many choices out there that I’m totally
    overwhelmed .. Any tips? Thanks a lot!

  2. However, we’ve been advised that we can not name ourselves
    “interest” beekeepers. Our official status is that of half-time beekeepers,
    per Dr. Medhat Nasr, as he said this during his discuss
    on the Vermont Beekeepers Association meeting a couple of years
    again.. Vermont Tourism Commissioner Wendy Knight to maneuver to personal sector.

    Vermont named in top 12 mountain bike locations in Singletracks article.
    Vermont has been named the craft beer capital of the United States.
    This will function an excellent wake up name for Vermont.
    This month, Carpenter sent an e mail to his workers:
    ‘You will not imagine this, however my cancer has come again,’ he stated, whereas outlining his
    intention to fight the good struggle. Last week,
    the Sanders marketing campaign said it had made greater than 7.Seventy five million calls
    and sent greater than sixty three million textual content messages,
    principally to voters in early states. An optimistic Sanders told the gang – estimated at 600
    by his campaign – “I consider we stand a very good chance of profitable in Iowa and along with your assist, we can win right here in New Hampshire. https://vtgeoalliance.org/

  3. Greetings I am so glad I found your web site, I really found you by mistake, while I was researching on Google for something else, Anyhow I am here now and would just like to say many thanks for a fantastic post and a all round entertaining blog (I also love the theme/design), I don’t have time to read through it all at the minute but I have saved it and also added in your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the great jo.| а

  4. Hi there I am so grateful I found your site, I really found you by error, while I was browsing on Yahoo for something else, Regardless I am here now and would just like to say many thanks for a tremendous post and a all round exciting blog (I also love the theme/design), I don’t have time to browse it all at the moment but I have book-marked it and also added in your RSS feeds, so when I have time I will be back to read much more, Please do keep up the great job.| а

  5. Pretty section of content. I just stumbled upon your weblog and in accession capital to assert that I acquire actually enjoyed account your blog posts. Anyway I will be subscribing to your feeds and even I achievement you access consistently fast.| а

  6. Excellent items from you, man. I’ve take into accout your stuff previous to and you’re simply too great. I really like what you have received right here, certainly like what you’re stating and the way in which in which you are saying it. You’re making it entertaining and you still care for to stay it wise. I can not wait to learn far more from you. That is actually a terrific web site.| а

  7. Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point. You definitely know what youre talking about, why waste your intelligence on just posting videos to your site when you could be giving us something enlightening to read?| а

  8. Its like you read my mind! You appear to know a lot about this, like you wrote the book in it or something. I think that you could do with some pics to drive the message home a little bit, but other than that, this is excellent blog. An excellent read. I’ll certainly be back.| а

  9. hello there and thank you for your information – I’ve definitely picked up anything new from right here. I did however expertise several technical points using this site, as I experienced to reload the web site lots of times previous to I could get it to load correctly. I had been wondering if your web hosting is OK? Not that I’m complaining, but slow loading instances times will sometimes affect your placement in google and can damage your high quality score if ads and marketing with Adwords. Well I am adding this RSS to my email and could look out for a lot more of your respective intriguing content. Ensure that you update this again soon.| а

  10. Hi there! I realize this is sort of off-topic but I needed to ask. Does running a well-established website such as yours take a lot of work? I am completely new to blogging however I do write in my journal everyday. I’d like to start a blog so I will be able to share my personal experience and feelings online. Please let me know if you have any kind of recommendations or tips for brand new aspiring blog owners. Thankyou!| а

  11. I’m extremely impressed with your writing skills and also with the layout on your blog. Is this a paid theme or did you modify it yourself? Either way keep up the excellent quality writing, it’s rare to see a great blog like this one today.| а

  12. Please let me know if you’re looking for a author for your weblog. You have some really good posts and I feel I would be a good asset. If you ever want to take some of the load off, I’d really like to write some content for your blog in exchange for a link back to mine. Please blast me an e-mail if interested. Many thanks!| а

  13. Drug And Alcohol Inpatient Rehab Near Me http://aaa-rehab.com Drug Rehab Near Me http://aaa-rehab.com Substance Abuse Assessment Near Me
    http://aaa-rehab.com

  14. Substance Use Assessment http://aaa-rehab.com Drug Rehab Near Me http://aaa-rehab.com Affordable Alcohol Rehab
    http://aaa-rehab.com

  15. Hello, I believe your blog could be having web browser compatibility problems. Whenever I look at your web site in Safari, it looks fine however, when opening in Internet Explorer, it’s got some overlapping issues. I simply wanted to give you a quick heads up! Apart from that, fantastic blog!| а

  16. I’m no longer positive wһere you’re ɡetting your
    іnformation, but ցreat topic. I needѕ to spend sⲟme tіmе finding out more оr workіng out more.
    Thanks for wonderful іnformation I was inn search off
    tһiѕ info foг my mission.

  17. With havin so much content do you ever run into any issues of plagorism or copyright violation? My blog has a lot of completely unique content I’ve either authored myself or outsourced but it appears a lot of it is popping it up all over the web without my permission. Do you know any ways to help reduce content from being ripped off? I’d truly appreciate it.| а

  18. Just desire to say your article is as amazing. The clearness in your submit is just cool and i can suppose you’re knowledgeable in this subject. Well along with your permission allow me to grasp your RSS feed to keep updated with drawing close post. Thank you one million and please carry on the enjoyable work.| а

  19. What i don’t understood is in truth how you’re not really a lot more well-appreciated than you might be right now. You’re very intelligent. You already know thus considerably with regards to this matter, made me in my opinion imagine it from numerous various angles. Its like women and men aren’t fascinated unless it’s one thing to accomplish with Lady gaga! Your individual stuffs great. Always care for it up!| а

  20. Hey! I just wanted to ask if you ever have any trouble with hackers? My last blog (wordpress) was hacked and I ended up losing several weeks of hard work due to no back up. Do you have any solutions to stop hackers?| а

  21. I do not know whether it’s just me or if perhaps everyone else encountering problems with your site. It seems like some of the written text on your posts are running off the screen. Can somebody else please comment and let me know if this is happening to them as well? This could be a problem with my internet browser because I’ve had this happen previously. Many thanks| а

  22. Hi there, simply changed into aware of your blog through Google, and located that it is truly informative. I’m gonna watch out for brussels. I will be grateful if you proceed this in future. A lot of other people will probably be benefited out of your writing. Cheers!| а

  23. This friend, Borja (let’s say he can be considered a follamigo), has had a dog, a German shepherd, for just over three years, I think. Now he is huge, young, strong and very big, because he also has him running all day (my friend is a super athlete).

  24. For me it is normal, but I wanted more, I wanted to know what else I saw, what dirty things excited him, my heart was beating very fast, I had to discover it soon before he arrived and the web was not helping me. beeg

  25. Excellent goods from you, man. I’ve understand your stuff previous to and you’re just too magnificent. I actually like what you’ve acquired here, certainly like what you are stating and the way in which you say it. You make it enjoyable and you still care for to keep it smart. I can not wait to read far more from you. This is actually a wonderful website.| а

  26. Pingback: viagra walmart
  27. Yesterday, while I was at work, my cousin stole my apple ipad and tested to see if it can survive a 30 foot drop, just so she can be a youtube sensation. My apple ipad is now broken and she has 83 views. I know this is totally off topic but I had to share it with someone!| а

  28. First of all I want to say awesome blog! I had a quick question that I’d like to ask if you don’t mind. I was interested to find out how you center yourself and clear your thoughts prior to writing. I’ve had trouble clearing my mind in getting my ideas out there. I truly do enjoy writing but it just seems like the first 10 to 15 minutes are usually lost just trying to figure out how to begin. Any suggestions or hints? Kudos!| а

  29. Howdy! This article couldn’t be written much better! Going through this post reminds me of my previous roommate! He always kept talking about this. I most certainly will forward this article to him. Pretty sure he will have a good read. Many thanks for sharing!| а

  30. She flicked off the lights and pushed him against the wall. A frame fell face down on the floor and shattered next to them. She didn’t allow him to take notice. She bit at his bottom lip, tugging his face, commanding him to follow her lead as she walked backwards down the hallway, deeper into the darkness, her darkness. And he, submitting to her bestial strength, simply weaved his fingers into the rust of her hair and bowed to her dominion. Like a lowly, unassuming insect, captivated by her hellfire glow.
    http://greenmodularhome.org/__media__/js/netsoltrademark.php?d=1080pxnxx.com
    Halfway down the hall, she slammed him back first into the wall again, harder than before — no frames there that time. She clawed at his chest under his shirt as she ate his kiss once more and bit down his neck; the groans he breathed out into the obscurity flinched sharply as her fangs grew less and less forgiving.

    His buckle caught the flash of lamplight seeping in from somewhere outside as she tugged at the leather strap of his belt. She ran her fingers up and back down the front of his chest, back down to his waist, and unclasped his belt, plucking it from the loops with a single sliding pull. Feverishly, she tore his pants open and moved them, along with his boxers, down just enough to reveal his smug erection. With both palms on his board-firm chest, she smirked at him, kissed him once more, a little softer than before, and squatted down before him.

  31. She flicked off the lights and pushed him against the wall. A frame fell face down on the floor and shattered next to them. She didn’t allow him to take notice. She bit at his bottom lip, tugging his face, commanding him to follow her lead as she walked backwards down the hallway, deeper into the darkness, her darkness. And he, submitting to her bestial strength, simply weaved his fingers into the rust of her hair and bowed to her dominion. Like a lowly, unassuming insect, captivated by her hellfire glow.
    http://www.integrityintimates.com/__media__/js/netsoltrademark.php?d=1080pxnxx.com
    Halfway down the hall, she slammed him back first into the wall again, harder than before — no frames there that time. She clawed at his chest under his shirt as she ate his kiss once more and bit down his neck; the groans he breathed out into the obscurity flinched sharply as her fangs grew less and less forgiving.

    His buckle caught the flash of lamplight seeping in from somewhere outside as she tugged at the leather strap of his belt. She ran her fingers up and back down the front of his chest, back down to his waist, and unclasped his belt, plucking it from the loops with a single sliding pull. Feverishly, she tore his pants open and moved them, along with his boxers, down just enough to reveal his smug erection. With both palms on his board-firm chest, she smirked at him, kissed him once more, a little softer than before, and squatted down before him.

  32. She flicked off the lights and pushed him against the wall. A frame fell face down on the floor and shattered next to them. She didn’t allow him to take notice. She bit at his bottom lip, tugging his face, commanding him to follow her lead as she walked backwards down the hallway, deeper into the darkness, her darkness. And he, submitting to her bestial strength, simply weaved his fingers into the rust of her hair and bowed to her dominion. Like a lowly, unassuming insect, captivated by her hellfire glow.
    http://stelcobreaker.com/__media__/js/netsoltrademark.php?d=1080pxnxx.com
    Halfway down the hall, she slammed him back first into the wall again, harder than before — no frames there that time. She clawed at his chest under his shirt as she ate his kiss once more and bit down his neck; the groans he breathed out into the obscurity flinched sharply as her fangs grew less and less forgiving.

    His buckle caught the flash of lamplight seeping in from somewhere outside as she tugged at the leather strap of his belt. She ran her fingers up and back down the front of his chest, back down to his waist, and unclasped his belt, plucking it from the loops with a single sliding pull. Feverishly, she tore his pants open and moved them, along with his boxers, down just enough to reveal his smug erection. With both palms on his board-firm chest, she smirked at him, kissed him once more, a little softer than before, and squatted down before him.

  33. Pingback: viagra 100mg
  34. She flicked off the lights and pushed him against the wall. A frame fell face down on the floor and shattered next to them. She didn’t allow him to take notice. She bit at his bottom lip, tugging his face, commanding him to follow her lead as she walked backwards down the hallway, deeper into the darkness, her darkness. And he, submitting to her bestial strength, simply weaved his fingers into the rust of her hair and bowed to her dominion. Like a lowly, unassuming insect, captivated by her hellfire glow.
    http://tfsports.com/__media__/js/netsoltrademark.php?d=1080pxnxx.com
    Halfway down the hall, she slammed him back first into the wall again, harder than before — no frames there that time. She clawed at his chest under his shirt as she ate his kiss once more and bit down his neck; the groans he breathed out into the obscurity flinched sharply as her fangs grew less and less forgiving.

    His buckle caught the flash of lamplight seeping in from somewhere outside as she tugged at the leather strap of his belt. She ran her fingers up and back down the front of his chest, back down to his waist, and unclasped his belt, plucking it from the loops with a single sliding pull. Feverishly, she tore his pants open and moved them, along with his boxers, down just enough to reveal his smug erection. With both palms on his board-firm chest, she smirked at him, kissed him once more, a little softer than before, and squatted down before him.

  35. Its like you read my mind! You seem to know so much about this, like you wrote the
    book in it or something. I think that you can do with some pics to drive the message home a bit, but other than that, this is magnificent blog.

    A fantastic read. I will certainly be back.

  36. She flicked off the lights and pushed him against the wall. A frame fell face down on the floor and shattered next to them. She didn’t allow him to take notice. She bit at his bottom lip, tugging his face, commanding him to follow her lead as she walked backwards down the hallway, deeper into the darkness, her darkness. And he, submitting to her bestial strength, simply weaved his fingers into the rust of her hair and bowed to her dominion. Like a lowly, unassuming insect, captivated by her hellfire glow.
    http://islandwellnesscenter.com/__media__/js/netsoltrademark.php?d=1080pxnxx.com
    Halfway down the hall, she slammed him back first into the wall again, harder than before — no frames there that time. She clawed at his chest under his shirt as she ate his kiss once more and bit down his neck; the groans he breathed out into the obscurity flinched sharply as her fangs grew less and less forgiving.

    His buckle caught the flash of lamplight seeping in from somewhere outside as she tugged at the leather strap of his belt. She ran her fingers up and back down the front of his chest, back down to his waist, and unclasped his belt, plucking it from the loops with a single sliding pull. Feverishly, she tore his pants open and moved them, along with his boxers, down just enough to reveal his smug erection. With both palms on his board-firm chest, she smirked at him, kissed him once more, a little softer than before, and squatted down before him.

  37. Howdy! I know this is somewhat off topic but I was wondering which blog platform are you using for
    this site? I’m getting fed up of WordPress because I’ve
    had problems with hackers and I’m looking at options for another platform.
    I would be great if you could point me in the direction of a good platform.

  38. cialis canada Cialis Without Doctor Prescription 94221671
    cialis generic price
    cialis professional cialis online buy cialis soft online
    buy cialis for daily use online 5mg Cialis 120193444 cialis 5mg price comparison
    where can i buy cialis Cialis Tadalafil 20 Mg 68429461
    order cialis no prescription
    buy cialis online cheap Cialis Without A Doctor’s Prescription 108871892
    buy cialis usa

  39. I like the helpful information you supply
    for your articles. I will bookmark your
    weblog and test once more here regularly. I’m fairly
    sure I will be informed many new stuff proper
    here! Good luck for the following!

  40. Its like you read my mind! You appear to know a lot about
    this, like you wrote the book in it or something. I think that you could do with
    a few pics to drive the message home a bit, but instead of that, this
    is wonderful blog. A fantastic read. I’ll certainly be back.

  41. After exploring a number of the articles on your website, I
    really like your technique of writing a blog. I added it to my bookmark site list and will be checking back soon. Please check
    out my web site too and let me know your opinion.

  42. Howdy just wanted to give you a quick heads up. The text in your article seem to be running off the screen in Ie. I’m not sure if this is a format issue or something to do with browser compatibility but I thought I’d post to let you know. The design and style look great though! Hope you get the issue resolved soon. Kudos| а

  43. Thanks for another excellent post. Where else may anybody get that type of info in such an ideal way of writing?
    I have a presentation subsequent week, and I’m on the look for such info.

  44. Pingback: generic viagra
  45. I do not even know the way I ended up right here, but I believed this put up used
    to be good. I don’t realize who you are however certainly
    you are going to a famous blogger for those who aren’t
    already. Cheers!

  46. Pingback: pills for ed
  47. Hello would you mind sharing which blog platform you’re working with? I’m going to start my own blog in the near future but I’m having a tough time making a decision between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I’m looking for something completely unique. P.S Sorry for being off-topic but I had to ask!| а

  48. I have been exploring for a bit for any high quality articles or weblog posts on this sort of house . Exploring in Yahoo I ultimately stumbled upon this web site. Studying this information So i am happy to express that I’ve a very good uncanny feeling I found out just what I needed. I such a lot unquestionably will make sure to do not disregard this website and give it a glance on a constant basis.| а

  49. Download any videos online like youtube videos, facebook videos,
    twitter videos, instagram videos and many more.

    traynote.com

  50. Hi, I do think this is an excellent blog.
    I stumbledupon it 😉 I will return yet again since i
    have saved as a favorite it. Money and freedom
    is the greatest way to change, may you be rich and continue to guide other people.

  51. Excellent website. Plenty of helpful information here.
    I am sending it to some buddies ans additionally sharing in delicious.
    And of course, thank you in your effort!

    Feel free to surf to my site: SEO

  52. Pingback: cialis generic
  53. Greetings! I know this is kind of off topic but I was wondering if you knew where I could find a captcha plugin for my comment form? I’m using the same blog platform as yours and I’m having problems finding one? Thanks a lot!| а

  54. Hi! I know this is somewhat off-topic but I needed to ask. Does operating a well-established website like yours take a massive amount work? I am brand new to writing a blog however I do write in my journal daily. I’d like to start a blog so I can easily share my own experience and thoughts online. Please let me know if you have any ideas or tips for new aspiring blog owners. Appreciate it!| а

  55. I was suggested this web site by my cousin. I am now not positive whether
    or not this submit is written via him as no one
    else realize such targeted about my difficulty. You are incredible!
    Thanks!

  56. Pingback: cheap ed pills
  57. “Enough!” He (I presumed) walked up to me, demonstrating that I had a head of height in advantage, and gestured imperiously for Bekhat to move away from me. Given the circumstances, what could she do but obey? As she did so, a pair of the brutes moved from their line to flank her, though they did nothing more.
    http://mediareport.org/__media__/js/netsoltrademark.php?d=1080pxvideos.com
    The little man drew from one sleeve a small stick, looking as though he had pulled it from a bush to scrape his teeth on his way over, and dragged it along the ground in a circle around me. As he completed the circle he spat on the joining and straightened again.

    I grew alarmed as he drew a small knife from somewhere or other, but he only cut his own finger and splashed a few drops on to the dust. “Now, creature,” he said, looking me in the eyes at last. “Simply walk over my circle, return to your home, prove me wrong, and we shall trouble you no more.”