Livio/ September 21, 2018/ Power BI, Power Query/ 2 comments

Converting a Number to Roman with Power Query

In this post I wanted to share my function to convert a number to Roman numerals. The function will accept any number from 1 to 3999 and convert it into a Roman number. No more excuses when at work you are asked to make the conversion 😉

 

 

Once you have saved the above as a new query called ToRoman, create another blank query with the below code:

 

Power Query Convert to Roman

 

2 Comments

  1. This is a great function (along with your combination generator) but you may not realize that to many of us this is extremely advanced PQ usage. Since we can’t step through functions in PQ to see what each step does, maybe you could write a blog post to explain each line.
    List.Accumulate is tough enough on its own, but when you have (state, current) and an “in” clause with [Roman – …] then you’re waaaaay ahead of the rest of us. I appreciate being able to copy and use the function but I’d certainly like to advance my understanding of it!

    1. Hi GMF,

      thanks for the feedback. I should have probably explained each step of the inner function does. It can be a bit confusion wrapping your head around List.Accumulate at the beginning, but it is the most efficient way to create loops in Power Query, along with List.Generate..
      Basically the part within “(state, current) =>” is itself a function which accepts two arguments and is called 4 times. This function calculates n Mod 10 and based on this results knows which letter to of the Roman number to return. The next time the function is called, n becomes n / 10 (integer divide), so the second time the function is called, the roman letter to apply is calculated as (n/10) Mod 10, and the third time is ((n/10) / 10) Mod 10. Let me know if you still have doubts!

Leave a Comment

Your email address will not be published.

*
*