Add the languages table to filter field parameters

As a content creator working with Power BI Desktop, there are many different ways to add a new table to a data model. In this article, you use Power Query to create a table named Languages.

Add the table

1. In Power BI Desktop, from the Home ribbon, select Transform data > Transform data to open the Power Query Editor.

2. Under Queries, right-click and select New Query > Blank Query from the context menu.

3. Select the new query. Under Query Settings > Properties > Name, enter Languages as the name of the query.

4. From the Home ribbon, select Advanced Editor.

5. Copy the following M code into the editor, then select Done.

   let
     LanguagesTable = #table(type table [
       Language = text,
       LanguageId = text,
       DefaultCulture = text,
       SortOrder = number
     ], {
       {"English", "en", "en-US", 1 },
       {"Spanish", "es", "es-ES", 2 },
       {"French", "fr", "fr-FR", 3 },
       {"German", "de", "de-DE", 4 }
     }),
     SortedRows = Table.Sort(LanguagesTable,{{"SortOrder", Order.Ascending}}),
     QueryOutput = Table.TransformColumnTypes(SortedRows,{{"SortOrder", Int64.Type}})
   in
     QueryOutput
   

   

   When this query runs, it generates the Languages table with a row for each of the four supported languages.

   

6. In the Home ribbon, select Close & Apply.

Create a relationship

Next, create a relationship between the Languages table and the Translated Product Names table created in Implement data translation using field parameters.

1. In Power BI Desktop, open the Model view.

2. Find the Languages table and the Translated Product Names table.

3. Drag the LanguageId column from one table to the LanguageId entry in the other table.

   

After you establish the relationship between Languages and Translated Product Names, it serves as the foundation for filtering the field parameter on a report-wide basis. For example, you can open the Filter pane and add the Language column from the Languages table to the Filters on all pages section. If you configure this filter with the Require single selection option, you can switch between languages using the Filter pane.

Related content

• Synchronize multiple field parameters