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 😉
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ( n as number ) as text => let Symbols = {"I", "V", "X", "L", "C", "D", "M"}, RomanSymbol = List.Accumulate( {0, 2, 4, 6}, [Roman = "", TempVal = n], (state, current) => let digit = Number.Mod( state[TempVal], 10 ), str = if digit >=1 and digit <= 3 then Text.Repeat( Symbols{current}, digit ) else if digit = 4 then Symbols{current} & Symbols{current + 1} else if digit >= 5 and digit <=8 then Symbols{current + 1} & Text.Repeat( Symbols{current}, Number.Mod(digit, 5) ) else if digit = 9 then Symbols{current} & Symbols{current + 2} else "" in [ Roman = str & state[Roman], TempVal = Number.IntegerDivide( state[TempVal], 10 ) ] ) in RomanSymbol[Roman] |
Once you have saved the above as a new query called ToRoman, create another blank query with the below code:
1 2 3 4 5 | let Source = Table.FromList( {1..3999}, Splitter.SplitByNothing(), type table [Number = Int64.Type] ), AddRoman = Table.AddColumn( Source, "Roman", each ToRoman( [Number] ), type text ) in AddRoman |
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!
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!