Create Date Table with Dynamic Language in Power Query

The Date table is an essential piece of every data model. And when you work with different users, some users look for one in English and others need a different calendar language. In this post you learn how you can create a date table to support different languages. Create one calendar that rules them all!

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 can address both in Power Query.

1. Changing Column Values

Date 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

By default Power Query produces the column values using the language on your local machine. And this is often the desired value. Yet imagine the machine that refreshes your dataset has a different system language. The scheduled refresh may then change the values of your columns to the language on the server. Or perhaps your colleague has a different language on his machine and refreshes the data. In both cases, the values may show up in a different language.

Yet you can avoid this. So how do you force a specific formatting you ask?

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"

You can look here to find the abbreviations that represent your language.

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 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 adding a step with:

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 ]
)
LanguageParameters

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. You can find more information on this list functions here.

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 for 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 setup 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 setup 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 in if-condition in each of the columns we create. This if-condition would have to include a branch for each language. The down-side to this is that it takes a lot of time to setup 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 add a column for each language you want to support.

This option is currently not available in Excel. In Excel you can achieve the same by importing a table from a worksheet.

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:

= Table.ColumnNames( LastStepName )

To setup 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 make sure to give these columns the language name you will provide in your parameter.
  • fill in the desired column names for each row.
  • Press OK
Translation Table for Flexible Calendar in Power Query

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
Move TranslationTable to Parameters 1

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 manually change a column name. This generates code 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 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 for making it all the way here. With all this in my you can now make use of your Date table. To see the Calendar in action you first add a query called CalendarLanguage(mentioned in chapter 1.3):

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

With that in place you can now 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"

Thanks for reading all the way here, and enjoy Power Query!

Recommend Reading

>>> Power Query - Foundations <<<List Functions (200+ examples)Text functions (150+ examples)Creating Tables from Scratch (40+ examples)Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

5 thoughts on “Create Date Table with Dynamic Language in Power Query”

    • Hi Azma,

      To add a new language you make 3 changes:
      1. You add the new language to the parameter of chapter 1.2.
      2. You add the language to the LanguageParameter step as explained in chapter 1.3.
      3. You add the translated column names to the translation table from chapter 2.2.

      Make sure the language from step 1, has the same name as the Language description in step 2 and the New language column name of step 3. With these steps you should be good to go!

      Reply
    • Hi Azma,

      I’ve provided the code in section 3.0. You can copy the last 2 queries into Power Query and that provides you with all the examples. You can create a blank query -> go to the advanced editor. And paste the codes in there.

      Hope that helps!
      Rick

      Reply

Leave a comment