Add a multilingual column to a dataset

This guide shows how to add a multilingual column to a dataset, using Power BI to retrieve language-specific translations.

By default, the code retrieves the English version, or if unavailable, selects the first available language in the list.

Below is the complete Power BI script, with optional sorting to retain only the latest state of each entity.

let
    // Replace these variables with your own information
    dataset_name = "FormTemplates",
    api_key = "your_api_key",
    organization_id = "your_organization_id",
    
    RetryRequest = (retries as number) =>
    let
        apiResponse = try Json.Document(Web.Contents("https://developer.finalcad.cloud/api/organizations/" & organization_id & "/data/config/dataseturl?name=" & dataset_name, 
            [Headers=[#"X-API-Key"= api_key, Authorization="token " & api_key]])),
        Source = if apiResponse[HasError] and retries > 0 then
            Function.InvokeAfter(() => @RetryRequest(retries - 1), #duration(0, 0, 0, 2))
        else Parquet.Document(Binary.Buffer(Web.Contents(apiResponse[Value][url])))
    in
        Source,

    Source = RetryRequest(3),

    // Optional sorting to retain only the latest state of each entity
    SortedRows = Table.Buffer(Table.Sort(Source, {{"updated_at", Order.Descending}})),
    DistinctRows = Table.Distinct(SortedRows, {"id"}),

    // Adding a column to handle multilingual data
    AddNameColumn = Table.AddColumn(DistinctRows, "name", each 
        let
            template = [template],
            namesList = try template[names] otherwise null,
            enRecord = if namesList <> null then List.First(List.Select(namesList, each _[localization] = "en")) else null,
            translationEn = if enRecord <> null then enRecord[translation] 
                            else if namesList <> null and List.Count(namesList) > 0 then namesList{0}[translation]
                            else null
        in
            translationEn
    )
in
    AddNameColumn

Explanation of Multilingual Code

• Optional Sorting Step: The SortedRows and DistinctRows steps are optional and only necessary if you want to retain the latest state of each entity.

• Multilingual Column Logic: The code adds a name column to the dataset. It first tries to select the English translation (en) if available. If no English translation is found, it defaults to the first language found in the list of translations.

This setup allows users to adapt the code to retrieve other languages or adjust the fallback logic as needed.