Create Date Table with Dynamic Language in Power Query

Do you want to make your data look organized and easy to understand for everyone, no matter what language they speak?

In this article, we will show you how to create a date table in Power Query that can change languages. That means, you can select your favourite language, and all the column names and values in the table will change to that language. It’s a helpful feature that makes it possible to have one calendar that rules them all. Even for your foreign colleagues. Let’s get started!

Table of contents

• 1. Changing Column Values
  • 1.1. Culture in Date Functions
  • 1.2. Create Language Parameter
  • 1.3. Add Culture Code as Query Step
  • 1.4. Reference Culture Value in Functions
• 2. Renaming Columns
  • 2.1. Create a Translation Table
  • 2.2. Add Translation Table to Query
  • 2.3. Rename Columns to Language
• 3. Date Table Script
• Conclusion

In an earlier post, you learned How to Create a Dynamic Date Table. This is an advanced post, so if this topic is new to you, make sure to read those fundamentals. Now, let’s delve into making a calendar flexible to support multiple languages.

There are two important areas to focus on when developing language flexibility in a calendar:

1. Changing Column Values
2. Renaming Columns

Let’s see how we can address both in Power Query.

1. Changing Column Values

Data tables can contain culture (language) specific values. This is often not the case for numbers (like year, month, week and day), but this is the case for text values like Day Names and Month Names.

And these values should show up in the right format and language. So, what is the best way to approach this with multiple languages?

1.1. Culture in Date Functions

Let’s say you want to create columns for Day Name and Month Day. By creating a custom column through the user interface, you may end up with the following:

= Date.DayOfWeekName( [Date] ) // "Monday", "Tuesday", "Wednesday", etc 
= Date.ToText( [Date], "ddd" ) // "Mon", "Tue", "Wed", etc 
= Date.MonthName( [Date] )     // "January", "February", "March" 
= Date.ToText( [Date], "MMM" ) // "Jan", "Feb", "Mar" etc

The output of these expressions provide English words and abbreviations. So how do you force a specific formatting respecting your desired local formatting conventions? Many functions in Power Query have an optional culture argument. To force a specific formatting, provide the functions with the relevant language culture, for example:

= Date.DayOfWeekName( [Date], "en-US" ) // "Monday", "Tuesday"
= Date.DayOfWeekName( [Date], "nl-NL" ) // "Maandag", "Dinsdag"

= Date.ToText( [Date], "ddd", "en-US" ) // "Mon", "Tue", "Wed"
= Date.ToText( [Date], "ddd", "fr-FR" ) // "lun.", "mar.", "mer"
= Date.ToText( [Date], "ddd", "es-ES" ) // "lu.", "ma.", "mi"

1.2. Create Language Parameter

So, different functions can leverage a culture code. The earlier examples have a hardcoded culture value included, and for a single language, this would be enough. Yet you can make the culture value dynamic based on the user selection.

Knowing you want the solution to be language-flexible, you first want the user to be able to select a language. To do that, you can create a parameter with a list of languages you support. You can do that by

• going to Manage Parameters, create a new parameter called CalendarLanguage
• Set Type to Text and Suggested Values to List
• Enter your languages in the Column interface provided
• Set the default value to your desired default value and press OK.

Please make sure these names align with the column names in your translation table. They will be used in the renaming function later.

The auto-generated code for my parameter is shown below. Make sure to name this parameter CalendarLanguage if you want to copy-paste my example.

// CalendarLanguage
"English"
  meta [
    IsParameterQuery         = true, 
    List                     = {"English", "Nederlands"}, 
    DefaultValue             = "English", 
    Type                     = "Text", 
    IsParameterQueryRequired = true
  ]

That’s all you need for the language parameter.

1.3. Add Culture Code as Query Step

With this in place, you can now add a separate step in your Date Table that contains the culture code. Name this step Culture.

The goal is to return the relevant culture code that belongs to the language your user selects. For a small number of languages, you can create a manual if-statement. In this case:

= if CalendarLanguage = "English"    then "EN-us" else 
  if CalendarLanguage = "Nederlands" then "nl-NL" else null

For a bigger number of languages, you can add a table showing language parameters by adding a step with the following:

Table.FromRows(
  Json.Document(
    Binary.Decompress(
      Binary.FromText(
        "i45Wcs1Lz8kszlDSUUrN03X1A9LOpUVFqXklSrE60Up+qSmpRT
         mJeSnFQIm8HF0/HyDtUZqZkpmeqhQbCwA=",
        BinaryEncoding.Base64
      ),
      Compression.Deflate
    )
  ),
  let
    _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
  in
    type table [ Language = _t, Culture = _t, DefaultValue = _t ]
)

So, how can you reference the values in this table? You need to know both the row index and the column of your desired value. To find the row index with the relevant language values, you can use the formula:

= List.PositionOf( LanguageParameters[Language] , CalendarLanguage )

CalendarLanguage here refers to the parameter we created earlier. With this in mind, referencing any value within that table then requires you to reference TableName[ColumnName]{RowIndex} like in the following:

= LanguageParameters[Culture]
{ List.PositionOf( LanguageParameters[Language] , CalendarLanguage ) }
// Returns the value of the [Culture] column for the relevant language.

= LanguageParameters[DefaultValue] 
{ List.PositionOf( LanguageParameters[Language] , CalendarLanguage ) }
// Returns value of the [DefaultValue] column for the relevant language.

Remember to give the steps a relevant name that you can reference in your functions.

1.4. Reference Culture Value in Functions

Then, instead of hardcoding the culture in your code, replace the culture by referencing the Culture value.

= Date.DayOfWeekName( [Date], "en-US" ) // "Monday", "Tuesday"
// the above code then becomes: 
= Date.DayOfWeekName( [Date], Culture ) // "Monday", "Tuesday"

For a bigger number of cultures, I recommend adding a table that contains all values specific to the language.

2. Renaming Columns

The next step in making your Calendar dynamic is changing the Column Names to the desired language.

If your model was set up earlier with a date table, it’s good to be aware of the following. Changing column names can break existing measures and relationships in your data model.

It is best to set up your calendar language at the start of any project. This issue does not arise when changing column values, which is a relatively safe action. Having said that, how can we make the renaming of columns dynamic?

One way is to put an if-condition in each of the columns we create. This if-condition would have to include a branch for each language. The downside to this is that it takes a lot of time to set up, and maintenance would be a pain. Including a new language would involve changing the if-statements for each column.

A better way is to create a table that contains all the default column names. Then, for each language, add a column with the translation of this text to your desired language. In the last step of your query, you can then rename all items in your calendar by referencing this translation table.

Adding a new language would simply involve creating a new column in this translation table and adjusting the parameter to include the language. What does that look like?

2.1. Create a Translation Table

The first step is to create a translation table. The easiest way to do that is by going to Enter Data and adding a column for each language you want to support.

Before performing those steps, let’s first retrieve all Column Names in the calendar. To retrieve the column names in your calendar, you can go to the last step and then add a new step that references the last step name.

If your latest step is called ‘LastStepName’, you can retrieve a list with column names by using the Table.ColumnNames function:

= Table.ColumnNames( LastStepName )

To set up your Translation table, you can now:

• Copy all the values in this list.
• Go to Enter Date (1).
• Create a column for each language you want to support, and give these columns the language name you will provide in your parameter.
• Fill in the desired column names for each row.
• Press OK

After performing these steps, you end up with a Table like above. The Power Query interface creates the Binary code for this table, which you can now copy from the formula bar. This can look something like this:

= Table.FromRows(
  Json.Document(
    Binary.Decompress(
      Binary.FromText(
        "ZZLBbsIwDIZfxeqZvkXRxFBBE4dpYhw81QU0GqSSbuLtZ7uJ63a9JP7/
        L65j53gsKoxUrGQZuuK0GgXYhEhn6rMBLxTxpvYHocivyEuOoaIWh1tM
        OhwihoY3zUjwV9a1mPzJTtT6HuKlTNlq5AOl5VQP9i14G34wwALZYUfm
        7xC7hQeHy72PMwK2oiywqX4Hzm/BUR/h3oIeY3IUpCg9o9CazzpkfQ0N
        LZAKnw+4BmMqPFNQwZDNYQ/vRN9h6L50BlmA3dB11BuU2iNba4zVKQdm
        ZaogyNvAEvWSbcy//WUB8ebzJ8i5Nu5y8uS35Qzg++Wx8NW0k58h72aE
        DSe702gEyX2cyhTOmulrTXhu0ZxeNi7B+m7/wfp8F/C+bR+U60yBeJrYT
        B/5N5zssWbn5yEYYUNwkBZphBbn3M1DL0f8bFY+cF6Dz+z1HJyL0+kP",
        BinaryEncoding.Base64
      ),
      Compression.Deflate
    )
  ),
  let
    _t = ((type nullable text) meta [Serialized.Text = true])
  in
    type table [English = _t, Nederlands = _t]
)

2.2. Add Translation Table to Query

To prepare the solution for the renaming, you can copy-paste the entire step (either your own or mine). I like having the translation table in the main query. To do that, you can go to your date table query and:

1. select the step where you want to insert the translation table.
2. click on the fx button to create a new step
3. paste the formula with your translation table
4. rename the step to TranslateColumnNames

2.3. Rename Columns to Language

As a final step of the query, you can perform the renaming. In this way, most of the query remains the same, and a single step converts all Columns to the required language. With the translation table in place, all we need is to prepare the renaming function.

The easiest way to do that is to change a column name manually. This generates code using Table.RenameColumns, which we can alter:

= Table.RenameColumns(
    #"Insert Is Weekday",
    { { "Date",  "Datum" } } 
)

The function uses lists with pairs of old and new values. You can reference your translation table using List.Zip with the following code:

= Table.RenameColumns(
    #"Insert Is Weekday",
    List.Zip(
        { TranslateColumnNames[English], // default column names (list)
          Table.Column(                  // new column names (list)
                TranslateColumnNames,    // from this table
                CalendarLanguage)        // and this Column Name
        }
    ),
    MissingField.Ignore // prevents throwing an error when columns miss
)

3. Date Table Script

Congratulations on making it all the way here. With all this in mind, you can now make use of your Date table. To see the Calendar in action, you first add a query called CalendarLanguage (mentioned in section 1.3):

// CalendarLanguage
"English"
  meta [
    IsParameterQuery         = true, 
    List                     = {"English", "Nederlands"}, 
    DefaultValue             = "English", 
    Type                     = "Text", 
    IsParameterQueryRequired = true
  ]

With that in place, you can create another query to generate the calendar. Paste the following code in the advanced editor:

let
    Today = Date.From( DateTime.LocalNow() ),
    StartDate = #date( 2021, 1, 1 ),
    EndDate = Date.EndOfYear( Today ),
    TranslateColumnNames = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZLRboMwDEV/xeJ18BdUU1exauKhmqo+eMK01UqQaNjUv58dEidh5QV870lwbnw8FjVaKkp5zUNxKhcBtsbSmaZgwCtZvDn7k1DkN+RXqKGmHueb9Tq0Fk3HH91C8FM1jZj8yJeozWjspfK7NcgLKt3TefA+D1+krpQDZT4OFF3EYeVBexknmxGwE2WFxeYTMD8CV5OFsQe3jMlF+EEDy5oY34arBNxcTUdPwRofd7gaJWs8k3GCgA7Ztns4EH2bkEQQ0jBE8zHKp4aoPcuCrGW3Q2zkY2aDppj37pcFxFv6Fw8lrt5+FT35eZUBfMpwUXzAeE1B10sKbrwiQUKSsTnhYpxJhx4P8eT0OjQPu+H9B7sZXsH7vr9T6NMX4rmN1UyrOGVqLz0nfoheCY0+gVyTSrjmEnd7d4cjHpkyLRKvw0fwJi7Ocbbg5cl8eVETOP0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [English = _t, Nederlands = _t]),
    LanguageParameters = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1Lz8kszlDSUUrN03X1A9LOpUVFqXklSrE60Up+qSmpRTmJeSnFQIm8HF0/HyDtUZqZkpmeqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Language = _t, Culture = _t, DefaultValue = _t]),
    CultureCode = LanguageParameters[Culture]  { List.PositionOf( LanguageParameters[Language] , CalendarLanguage ) },
    Defaultvalue = LanguageParameters[DefaultValue] { List.PositionOf( LanguageParameters[Language] , CalendarLanguage ) },
    #"List of Dates" = List.Dates( StartDate, Duration.Days( EndDate - StartDate ) +1, #duration( 1, 0, 0, 0 ) ),
    #"Converted to Table" = Table.FromList( #"List of Dates", Splitter.SplitByNothing(), type table[Date = Date.Type] ),
    #"Insert Date Integer" = Table.AddColumn(#"Converted to Table", "Date Integer", each Number.From( Date.ToText( [Date], "yyyyMMdd" ) ) , Int64.Type ) ,
    #"Insert Year" = Table.AddColumn(#"Insert Date Integer", "Year", each Date.Year([Date]), Int64.Type),
    // Creates a dynamic year value called 'Current' that moves with the current date. Put this value in a slicer and it automatically switches to the Current period.
    #"Add Year Default" = Table.AddColumn(#"Insert Year", "Year Default", each if Date.Year( Today ) = [Year] then Defaultvalue else Text.From( [Year] ), type text),
    #"Insert YYYY-MM" = Table.AddColumn(#"Add Year Default", "YYYY-MM", each Date.ToText( [Date], "yyyy-MM"), type text),
    #"Insert Month-Year" = Table.AddColumn(#"Insert YYYY-MM", "Month-Year", each Text.Proper( Date.ToText( [Date], "MMM yyyy", CultureCode ) ), type text),
    #"Insert Month Number" = Table.AddColumn(#"Insert Month-Year", "Month Number", each Date.Month([Date]), Int64.Type),
    #"Insert Month Name" = Table.AddColumn(#"Insert Month Number", "Month Name", each Text.Proper( Date.MonthName([Date], CultureCode ) ), type text),
    #"Insert Month Name Short" = Table.AddColumn(#"Insert Month Name", "Month Name Short", each Text.Proper( Date.ToText( [Date] , "MMM", CultureCode ) ), type text),
    // Creates a dynamic year value called 'Current' that moves with the current date. Put this value in a slicer and it automatically switches to the current period.
    #"Add Month Name Default" = Table.AddColumn(#"Insert Month Name Short", "Month Name Default", each if Date.Month( Today ) = [Month Number] then Defaultvalue else [Month Name], type text ),
    #"Insert Start of Month" = Table.AddColumn(#"Add Month Name Default", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Insert Start of Month", "End of Month", each Date.EndOfMonth( [Date] ), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Add ISO Week" = Table.AddColumn(#"Inserted Days in Month", "ISO Weeknumber", each let
CurrentThursday = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday ) ),
YearCurrThursday = Date.Year( CurrentThursday ),
FirstThursdayOfYear = Date.AddDays(#date( YearCurrThursday,1,7),- Date.DayOfWeek(#date(YearCurrThursday,1,1), Day.Friday) ),
ISO_Week = Duration.Days( CurrentThursday - FirstThursdayOfYear) / 7 + 1
in ISO_Week, Int64.Type ),
    #"Added Custom" = Table.AddColumn(#"Add ISO Week", "ISO W + Weeknumber", each "W" & Text.From( [ISO Weeknumber] ), type text ),
    #"Add ISO Year" = Table.AddColumn(#"Added Custom", "ISO Year", each let
CurrentThursday = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday ) ),
YearCurrThursday = Date.Year( CurrentThursday )
in YearCurrThursday, Int64.Type ),
    #"Insert Start of Week" = Table.AddColumn(#"Add ISO Year", "Start of Week", each Date.StartOfWeek([Date], Day.Monday ), type date),
    #"Insert Quarter Number" = Table.AddColumn(#"Insert Start of Week", "Quarter Number", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Quarter" = Table.AddColumn(#"Insert Quarter Number", "Quarter", each "Q" & Text.From( Date.QuarterOfYear([Date]) ), type text ),
    #"Add Year-Quarter" = Table.AddColumn(#"Added Quarter", "Year-Quarter", each Text.From( Date.Year([Date]) ) & "-Q" & Text.From( Date.QuarterOfYear([Date]) ), type text ),
    #"Insert Day Name" = Table.AddColumn(#"Add Year-Quarter", "Day Name", each Text.Proper( Date.DayOfWeekName([Date], CultureCode ) ), type text),
    #"Insert Day Name Short" = Table.AddColumn( #"Insert Day Name", "Day Name Short", each Text.Proper( Date.ToText( [Date], "ddd", CultureCode ) ), type text),
    #"Insert Day of Month Number" = Table.AddColumn(#"Insert Day Name Short", "Day of Month Number", each Date.Day([Date]), Int64.Type),
    #"Insert Day of Week" = Table.AddColumn(#"Insert Day of Month Number", "Day of Week Number", each Date.DayOfWeek([Date]), Int64.Type),
    #"Insert Day of Year" = Table.AddColumn(#"Insert Day of Week", "Day of Year Number", each Date.DayOfYear([Date]), Int64.Type),
    #"Add Day Offset" = Table.AddColumn(#"Insert Day of Year", "Day Offset", each Number.From( Date.From( Today ) - [Date] ) , Int64.Type ),
    #"Add Week Offset" = Table.AddColumn(#"Add Day Offset", "Week Offset", each ( Number.From( Date.StartOfWeek( [Date], Day.Monday) )- Number.From( Date.StartOfWeek( Today, Day.Monday ) ) ) / 7, Int64.Type ),
    #"Add Month Offset" = Table.AddColumn(#"Add Week Offset", "Month Offset", each ([Year] - Date.Year( Today ) ) *12 + ( [Month Number] - Date.Month( Today ) ), Int64.Type ),
    #"Add Quarter Offset" = Table.AddColumn(#"Add Month Offset", "Quarter Offset", each ( [Year] - Date.Year(Today) ) * 4 + Date.QuarterOfYear( [Date] ) - Date.QuarterOfYear( Today ), Int64.Type ),
    #"Add Year Offset" = Table.AddColumn(#"Add Quarter Offset", "Year Offset", each [Year] - Date.Year(Today), Int64.Type ),
    #"Insert Is Weekend" = Table.AddColumn(#"Add Year Offset", "Is Weekend", each if Date.DayOfWeek([Date], Day.Monday ) >= 5 then 1 else 0, Int64.Type ),
    #"Insert Is Weekday" = Table.AddColumn(#"Insert Is Weekend", "Is Weekday", each if Date.DayOfWeek([Date], Day.Monday ) < 5  then 1 else 0, Int64.Type ),
    #"Rename Columns to Language" = Table.RenameColumns(#"Insert Is Weekday", List.Zip( { List.Buffer( TranslateColumnNames[English] ), List.Buffer( Table.Column( TranslateColumnNames, CalendarLanguage ) ) } ), MissingField.Ignore )
in
    #"Rename Columns to Language"

Conclusion

So there you have it, a date table that can change languages. This is a great feature that makes your data easy to understand for everyone.

You’ve learned how to create a date table in Power Query, and how to make it change languages dynamically by using a language parameter, culture codes, and a translation table. Now, you can use it for your own projects, and share it with other people, who can also select their preferred language.

Thanks for reading all the way here. Enjoy Power Query!