List from table column as selectable items in function

jdellasala

Well-known Member
Joined
Dec 11, 2020
Messages
755
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Inspired by @smozgur's articles on using Google Translate in Power Query, I created functions to translate to and from English. They're working fine, and now I've discovered that using sl=auto will automatically detect the source language. In a test of translating an English Phrase into the 134 supported languages and then those translations back into English, 54 languages got it nearly perfect, and the other 80 were similar enough to pass.
In using the the function to Auto Detect the source language so that only the source text and target language are needed for the function, only 9 languages failed out of the 134 when compared to the two step translation to another language and back to English. Here's the function:
Power Query:
(SrcTxt as text, LangCode as text ) as text =>
// SrcTxt = Text to be translated, LangCode = the language CODE to translate to.
let
    URL = "https://translate.googleapis.com/translate_a/single?client=gtx&sl=auto&tl=" & LangCode & "&dt=t&q=" & SrcTxt,
    Source = Json.Document( Web.Contents( URL ) ),
    Translation = Source{0}{0}{0}
in
    Translation
Here's my question. I have a table of all the supported languages and their codes. How can I have the function present the list of languages like a Data Validation list when invoking the function? Right now one needs to either manually specify the Language Code or it needs to be in a column. Is that even possible?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I don't think that functionality is available. If you give your second parameter an insanely long name then you can use the intellisense prompt as a key for what to enter as the code. Kind of a crazy work-around:

Power Query:
(SrcTxt as text, #"af Afrikaans, ak Akan, sq Albanian, am Amharic, ar Arabic, hy Armenian, az Azerbaijani, eu Basque, be Belarusian, bem Bemba, bn Bengali, bh Bihari, bs Bosnian, br Breton, bg Bulgarian, km Cambodian, ca Catalan, chr Cherokee, ny Chichewa, zh-CN Chinese (Simplified), zh-TW Chinese (Traditional), co Corsican, hr Croatian, cs Czech, da Danish, nl Dutch, en English, eo Esperanto, et Estonian, ee Ewe, fo Faroese, tl Filipino, fi Finnish, fr French, fy Frisian, gaa Ga, gl Galician, ka Georgian, de German, el Greek, gn Guarani, gu Gujarati, ht Haitian Creole, ha Hausa, haw Hawaiian, iw Hebrew, hi Hindi, hu Hungarian, is Icelandic, ig Igbo, id Indonesian, ia Interlingua, ga Irish, it Italian, ja Japanese, jw Javanese, kn Kannada, kk Kazakh, rw Kinyarwanda, rn Kirundi, xx-klingon Klingon, kg Kongo, ko Korean, kri Krio (Sierra Leone), ku Kurdish, ckb Kurdish (Soranî), ky Kyrgyz, lo Laothian, la Latin, lv Latvian, ln Lingala, lt Lithuanian, loz Lozi, lg Luganda, ach Luo, mk Macedonian, mg Malagasy, ms Malay, ml Malayalam, mt Maltese, mi Maori, mr Marathi, mfe Mauritian Creole, mo Moldavian, mn Mongolian, sr-ME Montenegrin, ne Nepali, pcm Nigerian Pidgin, nso Northern Sotho, no Norwegian, nn Norwegian (Nynorsk), oc Occitan, or Oriya, om Oromo, ps Pashto, fa Persian, xx-pirate Pirate, pl Polish, pt-BR Portuguese (Brazil), pt-PT Portuguese (Portugal), pa Punjabi, qu Quechua, ro Romanian, rm Romansh, nyn Runyakitara, ru Russian, gd Scots Gaelic, sr Serbian, sh Serbo-Croatian, st Sesotho, tn Setswana, crs Seychellois Creole, sn Shona, sd Sindhi, si Sinhalese, sk Slovak, sl Slovenian, so Somali, es Spanish, es-419 Spanish (Latin American), su Sundanese, sw Swahili, sv Swedish, tg Tajik, ta Tamil, tt Tatar, te Telugu, th Thai, ti Tigrinya, to Tonga, lua Tshiluba, tum Tumbuka, tr Turkish, tk Turkmen, tw Twi, ug Uighur, uk Ukrainian, ur Urdu, uz Uzbek, vi Vietnamese, cy Welsh, wo Wolof, xh Xhosa, yi Yiddish, yo Yoruba, zu Zulu"
 as text ) as text =>
// SrcTxt = Text to be translated, second parameter = the language CODE to translate to.
let
    LangCode = #"af Afrikaans, ak Akan, sq Albanian, am Amharic, ar Arabic, hy Armenian, az Azerbaijani, eu Basque, be Belarusian, bem Bemba, bn Bengali, bh Bihari, bs Bosnian, br Breton, bg Bulgarian, km Cambodian, ca Catalan, chr Cherokee, ny Chichewa, zh-CN Chinese (Simplified), zh-TW Chinese (Traditional), co Corsican, hr Croatian, cs Czech, da Danish, nl Dutch, en English, eo Esperanto, et Estonian, ee Ewe, fo Faroese, tl Filipino, fi Finnish, fr French, fy Frisian, gaa Ga, gl Galician, ka Georgian, de German, el Greek, gn Guarani, gu Gujarati, ht Haitian Creole, ha Hausa, haw Hawaiian, iw Hebrew, hi Hindi, hu Hungarian, is Icelandic, ig Igbo, id Indonesian, ia Interlingua, ga Irish, it Italian, ja Japanese, jw Javanese, kn Kannada, kk Kazakh, rw Kinyarwanda, rn Kirundi, xx-klingon Klingon, kg Kongo, ko Korean, kri Krio (Sierra Leone), ku Kurdish, ckb Kurdish (Soranî), ky Kyrgyz, lo Laothian, la Latin, lv Latvian, ln Lingala, lt Lithuanian, loz Lozi, lg Luganda, ach Luo, mk Macedonian, mg Malagasy, ms Malay, ml Malayalam, mt Maltese, mi Maori, mr Marathi, mfe Mauritian Creole, mo Moldavian, mn Mongolian, sr-ME Montenegrin, ne Nepali, pcm Nigerian Pidgin, nso Northern Sotho, no Norwegian, nn Norwegian (Nynorsk), oc Occitan, or Oriya, om Oromo, ps Pashto, fa Persian, xx-pirate Pirate, pl Polish, pt-BR Portuguese (Brazil), pt-PT Portuguese (Portugal), pa Punjabi, qu Quechua, ro Romanian, rm Romansh, nyn Runyakitara, ru Russian, gd Scots Gaelic, sr Serbian, sh Serbo-Croatian, st Sesotho, tn Setswana, crs Seychellois Creole, sn Shona, sd Sindhi, si Sinhalese, sk Slovak, sl Slovenian, so Somali, es Spanish, es-419 Spanish (Latin American), su Sundanese, sw Swahili, sv Swedish, tg Tajik, ta Tamil, tt Tatar, te Telugu, th Thai, ti Tigrinya, to Tonga, lua Tshiluba, tum Tumbuka, tr Turkish, tk Turkmen, tw Twi, ug Uighur, uk Ukrainian, ur Urdu, uz Uzbek, vi Vietnamese, cy Welsh, wo Wolof, xh Xhosa, yi Yiddish, yo Yoruba, zu Zulu",
    URL = "https://translate.googleapis.com/translate_a/single?client=gtx&sl=auto&tl=" & LangCode & "&dt=t&q=" & SrcTxt,
    Source = Json.Document( Web.Contents( URL ) ),
    Translation = Source{0}{0}{0}
in
    Translation
 
Upvote 0
I don't think that functionality is available. If you give your second parameter an insanely long name then you can use the intellisense prompt as a key for what to enter as the code. Kind of a crazy work-around:

Power Query:
(SrcTxt as text, #"af Afrikaans, ak Akan, sq Albanian, am Amharic, ar Arabic, hy Armenian, az Azerbaijani, eu Basque, be Belarusian, bem Bemba, bn Bengali, bh Bihari, bs Bosnian, br Breton, bg Bulgarian, km Cambodian, ca Catalan, chr Cherokee, ny Chichewa, zh-CN Chinese (Simplified), zh-TW Chinese (Traditional), co Corsican, hr Croatian, cs Czech, da Danish, nl Dutch, en English, eo Esperanto, et Estonian, ee Ewe, fo Faroese, tl Filipino, fi Finnish, fr French, fy Frisian, gaa Ga, gl Galician, ka Georgian, de German, el Greek, gn Guarani, gu Gujarati, ht Haitian Creole, ha Hausa, haw Hawaiian, iw Hebrew, hi Hindi, hu Hungarian, is Icelandic, ig Igbo, id Indonesian, ia Interlingua, ga Irish, it Italian, ja Japanese, jw Javanese, kn Kannada, kk Kazakh, rw Kinyarwanda, rn Kirundi, xx-klingon Klingon, kg Kongo, ko Korean, kri Krio (Sierra Leone), ku Kurdish, ckb Kurdish (Soranî), ky Kyrgyz, lo Laothian, la Latin, lv Latvian, ln Lingala, lt Lithuanian, loz Lozi, lg Luganda, ach Luo, mk Macedonian, mg Malagasy, ms Malay, ml Malayalam, mt Maltese, mi Maori, mr Marathi, mfe Mauritian Creole, mo Moldavian, mn Mongolian, sr-ME Montenegrin, ne Nepali, pcm Nigerian Pidgin, nso Northern Sotho, no Norwegian, nn Norwegian (Nynorsk), oc Occitan, or Oriya, om Oromo, ps Pashto, fa Persian, xx-pirate Pirate, pl Polish, pt-BR Portuguese (Brazil), pt-PT Portuguese (Portugal), pa Punjabi, qu Quechua, ro Romanian, rm Romansh, nyn Runyakitara, ru Russian, gd Scots Gaelic, sr Serbian, sh Serbo-Croatian, st Sesotho, tn Setswana, crs Seychellois Creole, sn Shona, sd Sindhi, si Sinhalese, sk Slovak, sl Slovenian, so Somali, es Spanish, es-419 Spanish (Latin American), su Sundanese, sw Swahili, sv Swedish, tg Tajik, ta Tamil, tt Tatar, te Telugu, th Thai, ti Tigrinya, to Tonga, lua Tshiluba, tum Tumbuka, tr Turkish, tk Turkmen, tw Twi, ug Uighur, uk Ukrainian, ur Urdu, uz Uzbek, vi Vietnamese, cy Welsh, wo Wolof, xh Xhosa, yi Yiddish, yo Yoruba, zu Zulu"
 as text ) as text =>
// SrcTxt = Text to be translated, second parameter = the language CODE to translate to.
let
    LangCode = #"af Afrikaans, ak Akan, sq Albanian, am Amharic, ar Arabic, hy Armenian, az Azerbaijani, eu Basque, be Belarusian, bem Bemba, bn Bengali, bh Bihari, bs Bosnian, br Breton, bg Bulgarian, km Cambodian, ca Catalan, chr Cherokee, ny Chichewa, zh-CN Chinese (Simplified), zh-TW Chinese (Traditional), co Corsican, hr Croatian, cs Czech, da Danish, nl Dutch, en English, eo Esperanto, et Estonian, ee Ewe, fo Faroese, tl Filipino, fi Finnish, fr French, fy Frisian, gaa Ga, gl Galician, ka Georgian, de German, el Greek, gn Guarani, gu Gujarati, ht Haitian Creole, ha Hausa, haw Hawaiian, iw Hebrew, hi Hindi, hu Hungarian, is Icelandic, ig Igbo, id Indonesian, ia Interlingua, ga Irish, it Italian, ja Japanese, jw Javanese, kn Kannada, kk Kazakh, rw Kinyarwanda, rn Kirundi, xx-klingon Klingon, kg Kongo, ko Korean, kri Krio (Sierra Leone), ku Kurdish, ckb Kurdish (Soranî), ky Kyrgyz, lo Laothian, la Latin, lv Latvian, ln Lingala, lt Lithuanian, loz Lozi, lg Luganda, ach Luo, mk Macedonian, mg Malagasy, ms Malay, ml Malayalam, mt Maltese, mi Maori, mr Marathi, mfe Mauritian Creole, mo Moldavian, mn Mongolian, sr-ME Montenegrin, ne Nepali, pcm Nigerian Pidgin, nso Northern Sotho, no Norwegian, nn Norwegian (Nynorsk), oc Occitan, or Oriya, om Oromo, ps Pashto, fa Persian, xx-pirate Pirate, pl Polish, pt-BR Portuguese (Brazil), pt-PT Portuguese (Portugal), pa Punjabi, qu Quechua, ro Romanian, rm Romansh, nyn Runyakitara, ru Russian, gd Scots Gaelic, sr Serbian, sh Serbo-Croatian, st Sesotho, tn Setswana, crs Seychellois Creole, sn Shona, sd Sindhi, si Sinhalese, sk Slovak, sl Slovenian, so Somali, es Spanish, es-419 Spanish (Latin American), su Sundanese, sw Swahili, sv Swedish, tg Tajik, ta Tamil, tt Tatar, te Telugu, th Thai, ti Tigrinya, to Tonga, lua Tshiluba, tum Tumbuka, tr Turkish, tk Turkmen, tw Twi, ug Uighur, uk Ukrainian, ur Urdu, uz Uzbek, vi Vietnamese, cy Welsh, wo Wolof, xh Xhosa, yi Yiddish, yo Yoruba, zu Zulu",
    URL = "https://translate.googleapis.com/translate_a/single?client=gtx&sl=auto&tl=" & LangCode & "&dt=t&q=" & SrcTxt,
    Source = Json.Document( Web.Contents( URL ) ),
    Translation = Source{0}{0}{0}
in
    Translation
@JGordon11 - you know I have a lot of respect for your M Code chops - I've often given you likes on your contributions. However the list of countries is currently 134 entries, dynamically retrieved from the web, and I understand how to set up a list of options manually, but want to be able to just use the list of countries from another query, and preferably have it appear as a drop down list in the Add Column - Invoke Custom Function dialog like a Data Validation List:
1683801966132.png

The two column table of Countries and Codes is LanguageCodes, and the list of countries derived from that list is called Languages which is one line of M Code:
Power Query:
Source = List.Distinct(LanguageCodes[Language]).

If it can't be done, so be it, but thanks regardless.
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,658
Latest member
GStorm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top