Have you ever had RGBA values stored in text format that need converting to actual, usable RGBA colour values?
I had this use case recently. It took a bit of Power Fx skullduggery so thought I’d share the method!
In my scenario, the client had a table with RGBA values stored in a data source. The values were stored in Single Line of Text columns. Here’s an example of what I was working with – note the inconsistent spacing between each value:
Solution
The solution was to combine a mixture of the First, FirstN, Last, Split, Substitute & Lower functions.
The Substitute function acts to remove the brackets and any spaces, Split then creates an object of references where a new line is created for every comma in the string. We use this in conjunction with Lower to ensure the formula will always work regardless of whether the source data has RGBA in upper or lower case. We can then use First, Last/FirstN and Last to determine after what number comma do we need the value from.
Did all that make sense? Maybe not. It made sense when I worked through it! If you don’t care about the method to the madness and just want the Power Fx, please see below. The example is using a gallery hence the ThisItem.RGBATextValue reference, so update to your text string/variable/collection output as required:
RGBA(
// Get the value for R
First(
Split(
Substitute(
Substitute(
Lower(
Substitute(ThisItem.RGBATextValue," ","")),
"rgba(",""),")",""),",")).Value,
// Get the value for G
Last(FirstN(
Split(
Substitute(
Substitute(
Lower(
Substitute(ThisItem.RGBATextValue," ","")),
"rgba(",""),")",""),","),2)).Value,
// Get the value for B
Last(FirstN(
Split(
Substitute(
Substitute(
Lower(
Substitute(ThisItem.RGBATextValue," ","")),
"rgba(",""),")",""),","),3)).Value,
// Get the value for A
Last(
Split(
Substitute(
Substitute(
Lower(
Substitute(ThisItem.RGBATextValue," ","")),
"rgba(",""),")",""),",")).Value)
Here’s my example in all its working glory:
