Power Query & Power BI are ideal learning paths from Excel to R

Reading Jen Stirrup’s great article about the learning path for SQL Server 2016 and R I ended up learning my first R pieces from this wonderful post that she has referenced. There Tony Ojeda beautifully describes how basic Excel-concepts are translated into R.

Well – I cannot speak of so much personal experience here – but these R-codes came easy as nothing before to me. Apart from Tony’s first class didactics, could this be due to what I’ve learned in Power Query over the last year?

I’ve added the comparison of Excel and R by the M-code from Power Query – putting it in the middle:


Do you find the similarities as striking as I do?

So if you are an Excel-user who still struggles a bit with R, consider Power Query as a bridge here. Read the article where all these steps are described and try to follow with R. Parallel you can follow the Power Query solution with the enclosed file. You can watch how the steps look and how the data evolves accordingly by clicking on every step in the “Query Settings” pane within the Query Editor:


The convenient aspect of learning Power Query as an Excel user is that you start with “just clicking”: The basic steps will be done using Power Query’s nice user interface. Your steps will be “recorded” by being translated into the M-language. Normally there is no need to watch the code, but once the fever has got you, you will want more. And then you will simply start with adjusting some elements in the existing lines of codes (see how this goes in Gil Ravi’s recent article for example). Quite a smooth way to get a feeling for the structure and logic of the language and adjust to a language-oriented way of thinking.

If you are a programmer, you might not be so impressed, because: Programming languages might just all work similar in a way. But if you are – like me – an Excel pro with some DB (Access)-knowledge: Learn Power Query! It’s not only the coolest tool ever invented and will help you tremendously with your Excel-tasks – it will also help you to enter the hot spots of the future like Data Science (R works in Power BI and SSRS 2016!). If you’re interested in good coursed for Data Science itself, please check out this article.

Link to some learning sources for Power Query. Start with Bill Jelens video and then read this book. It’s the best starting point for Excel users.



Warning: I’ve cut the sample data (file size), so this workbook will not deliver the same results as in the other blog post. If you’re adventurous, you can download the txt-file with the full sample data and adjust the query to import it 🙂 diamonds.txt

Enjoy & stay queryious 🙂

Comments (3) Write a comment

  1. Hi Imke,

    Really like all the articles on you’re site.
    Just started learning R programming. On http://www.edx.org there is a course about Power BI and also a wonderful course on R-Programming. The ability to load R-scripts in Power BI is really cool and opens new doors for data enthusiasts like us.

    Danke viel mal and keep up the good work.

    Ronald van Bilsem


    • Hi Ronald,
      thank you very much for this nice feedback.
      This site is awesome – thanks for sharing the link!
      Totally share your enthusiasm about the R-integration.
      Stay queryious 🙂


  2. Hi Ronald, hi Imke,

    I´ve got two book tipps for you:
    1) http://www.amazon.de/gp/product/111866146X/ref=pd_lpo_sbs_dp_ss_2?pf_rd_p=556245207&pf_rd_s=lpo-top-stripe&pf_rd_t=201&pf_rd_i=3527760601&pf_rd_m=A3JWKAKR8XB7XF&pf_rd_r=1Y2E4NE00KBPZ98FDZB0

    Chapter 10 contents “Moving from Spreadsheets into R”. There’s nothing more to be said.

    2) http://www.amazon.de/Datendesign-mit-R-100-Visualisierungsbeispiele/dp/3955390942/ref=pd_sim_14_4?ie=UTF8&dpID=410PtKMqcpL&dpSrc=sims&preST=_AC_UL160_SR118%2C160_&refRID=1GYN0MM9MAPP6PPT8QD2

    @ Ronald: This book is written in German, but there are 100 case studies explained and you can download the scripts!

    Tot gauw & bis demnächst, Rüdiger


Leave a Reply