Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Replace values in several columns and in several tables (power query)

Hey,

 

I have several tables with columns which has values (oftenly numbers) that I would like to translate/replace with other values (oftenly text), for instance gender, level, status... See example below, translations tables,

new_to_bi_0-1661526429975.png

new_to_bi_1-1661526519098.png

and many other tables with such columns as gender, level... that need to be translated according to my translation tables above.

What is the best solution in this case, I'm searching for a generic solution which I can reuse on several tables and for different columns. Like a function which takes as input (column to be translated in whatever table, translation table) and returns a column in the same table with the translated values.

Thanks,

4 REPLIES 4
dcrow5378
Helper II
Helper II

You could do this by having a lookup table joined to the fact table and then use the RELATED function in a calculated column.

Or you could use the replace in Power Query Editor.

Or you could use the SWITCH function.

Anonymous
Not applicable

Thanks for your respond.
I'm not sure but I think all your suggestions are good in case of one fact table, in my case I have several tables and I want to avoid repeating same steps for each table, like joining tables and writing a specific function for a specific table, or using replace which means doing that for each column and each table, or Switch which also mean manually writing down values with their replacments several times. 
I have about 30 fact tables where each one have at least one column to be translated... 

You could also use LOOKUPVALUE, but they would need to be the same datatype, I believe.  You are going to have to choose to do something with these tables to get the columns you need added.  Are you loading lookup tables into your model?

Anonymous
Not applicable

yes

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.