Replace Matches function returning custom columns?
Hello.
I have a large table in which I would like to replace certain values with a lookup from a static dataset. (Example below, I'd like to replace the "..." placeholder values with the actual correct ones from the static dataset)
Basically what I would need is the option to choose the return column dynamically so that I don't have to write the whole function for tens of different columns, just maybe make 1 custom function which references my static dataset, and I only need to select the value to return (since my lookup table,key and lookup column would be the same every time, and a lookup key you can define to be anything anyway.).
I could theoretically use a join and make a custom IF function to achieve this, but it would need some cleanup of the data, and I really have a lot of columns to do this on so I'd prefer to keep my step results clean instead of adding 20-30 extra columns only to then throw them out.
Is there an easy way to do this using a custom lookup function or should I resort to joining?
Answers
Hi Richard
I think the way to do this is to isolate all of the '...' values so that they can be replaced
Hi @Richard Illyes,
Thanks for your post. I've just had a stab at this using @Josh Boxer's recommendation as a starting point and was able to get this to work. To bring this to life a bit I've created a .dmxd file you can import to explore the solution for yourself, and have summarised the steps in this post too:
Then use the Rows to columns to flip the data back so it looks like this:
And then finally use a Group and a Sort to present it in a more suitable manner:
To help understand this more, I've attached the .dmxd file for my solution which you should be able to easily import into your instance of Data Studio (note that you'll need to be running version 2.9.4 or above).