How to match my column with another table to trasnalte

[Anonymous]

helpThe title can be confusing, i have 2 tables, 1 is transaltion 

English	French	German	Dutch
Product stability	Stabilité du produit	Produktstabilität	Productstabiliteit
Product UI	Porduit UI	Produkt UI	Gebruikersgemak
Missing features	Caractéristiques manquantes	Fehlende Merkmale	Nieuwe functionaliteit
Technical Support	Support technique	Technische Unterstützung	Technische support
Implementation Services	Services de mise en oevre	Implementierungsdienste	Implementatie
Other	Autres	Andere	Iets ander

the otehr table is my feedback which has 1 column only called mutiple choice

how can i create a new calumn called transalted answers to see if the multiple answer matches with english column, then keep the same else if multiple answer matches with french, then return english equivilent else if multiple answer matches with dutch, then return english equivilent else if multiple answer matches with german, then return english equivilent

[dufoq3]

Hi @Anonymous, different approach.

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY9BDoJADEWv0rD2EkZYsJAY0BVhUYcCjTDItGOiN/IO7riYZFiIXfw0r7/9aVlGUbUro5Mba28UCsUr96zPQI8swrYFgIZQvSMJOMiZTGdZTEew1sUqOdH5oy9v2+BJh3tPA1llcguTmsmK0v8MlUcLBbkHm20AZjHlSWgP6NDo/HYsypMngQHt5HFJ3Cz0pAL7LE7y30s3hUu6nqsbrKWJquoL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Multiple_choice_1__c = _t]),
    TranslationTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VVBBTsMwEPzKKud+okIC9VBAKj1VObjOtFklcdLddRG8hwt/4NaPYSdpobfdmfHMrHe74lX6KnojNbfnlu2jWBSbeb58UxVpyAq2hI/axq7Sy9cN9DcQSVku/ny3q6zpJVvMy+QyLU/YS+QGokd0rhmfrlmVw5EOcBYFmmQPTpxPfYTV+BSh1Llwii7YSD+ibhEq0BrSdK5Fwp4Z8R10iMEb98H96/YGXwf2rqVNHIZe8hnzRDZyKSJhk059DdqmJFG7/NhnDMd7TmeTbL3qhhYdgrkcShvImf3Y8TpSqtmxghCox1ly0O0VQ5K9VoygdsckP4wJL1ZDErOMNv3NMh0+urQwJZe3oix/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [English = _t, French = _t, German = _t, Dutch = _t]),
    TranslationList = Table.ToList(TranslationTable, Combiner.CombineTextByDelimiter("||")),
    Ad_EnglishTranslation = Table.AddColumn(Source, "English Translation", each 
        [ a = Text.Combine(List.RemoveMatchingItems(Text.Split(Text.Trim([Multiple_choice_1__c]), " "), {null, "", " "}), " "),
          b = List.Select(TranslationList, (x)=> Text.Contains(x, a, Comparer.OrdinalIgnoreCase)),
          c = if b = {} then "**MISSING IN TRANSLATION**" else if Text.Length(a) = 0 then null else Text.BeforeDelimiter(b{0}?, "||")
        ][c], type text)
in
    Ad_EnglishTranslation

[AlienSx · Accepted solution]

let
    translation = translation_table,
    multiple_choice = multiple_choice_table,
    dict = Record.FromList(
        List.Repeat(translation[English],4),
        List.Combine(Table.ToColumns(translation))
    ),
    translated = Table.AddColumn(
        multiple_choice, 
        "translated answers", 
        each Record.FieldOrDefault(
            dict, 
            _[Multiple choice] ?? "", 
            _[Multiple choice]
        )
    )
in
    translated