BLOG RESOURCES SITEMAP CONTACT ABOUT

Create Date Table with Dynamic WRIT T EN BY RICK D E GROOT

Language in Power Query Rick is the founder of BI Gorilla. He believes learning is one of
life's greatest pleasures and shares his knowledge to help you
U p date d on Fe b r u a r y 2 6, 2 0 24 9 Power Query
improve your skills.

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

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

Table of contents GET STARTED

I will never sell your information for any reason.

Subscribe to my Channel

Master the M language?

···

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:

Recommended Reading

What is Power Query

(Comprehensive Guide)

··· Removing Duplicates (Basics to

Advanced)

Understanding Records in
Power Query

Best Resources to learn Power

BI
1. Changing Column Values
Master Custom Format Strings
2. Renaming Columns in Power Query

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

1. Changing Column Values Recent Posts

Mastering M: Which Power

Data tables can contain culture (language) specific values. This is often not the case for
Query Functions to Learn and
numbers (like year, month, week and day), but this is the case for text values like Day
When
Names and Month Names.
SWITCH Function (Multiple If
Statements) in Power Query
And these values should show up in the right format and language. So, what is the best
Perform Join Based on a Date
way to approach this with multiple languages?
Range in Power Query

Remove Milliseconds from Time

1.1. Culture in Date Functions
Values in Power Query

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

···

= 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

Important

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
using culture codes.

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"

Tip

You can look here to find the abbreviations that represent your language. And this article also
delves into both formatting and culture codes.

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.

Tip

You can find more information on 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 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.

···

Note

The ‘Enter Data’ functionality is currently not available in Excel. Yet, 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 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+eMK
LanguageParameters = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1Lz8kszlDSUUrN03X1A9LOpUVFqXklS
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], "yy
#"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 autom
#"Add Year Default" = Table.AddColumn(#"Insert Year", "Year Default", each if Date.Year( Today ) = [Year] then Defaultvalu
#"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",
#"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], Cult
#"Insert Month Name Short" = Table.AddColumn(#"Insert Month Name", "Month Name Short", each Text.Proper( Date.ToText( [Dat
// Creates a dynamic year value called 'Current' that moves with the current date. Put this value in a slicer and it autom
#"Add Month Name Default" = Table.AddColumn(#"Insert Month Name Short", "Month Name Default", each if Date.Month( Today )
#"Insert Start of Month" = Table.AddColumn(#"Add Month Name Default", "Start of Month", each Date.StartOfMonth([Date]), ty
#"Inserted End of Month" = Table.AddColumn(#"Insert Start of Month", "End of Month", each Date.EndOfMonth( [Date] ), type
#"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int6
#"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 te
#"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 ), ty
#"Insert Quarter Number" = Table.AddColumn(#"Insert Start of Week", "Quarter Number", each Date.QuarterOfYear([Date]), Int
#"Added Quarter" = Table.AddColumn(#"Insert Quarter Number", "Quarter", each "Q" & Text.From( Date.QuarterOfYear([Date]) )
#"Add Year-Quarter" = Table.AddColumn(#"Added Quarter", "Year-Quarter", each Text.From( Date.Year([Date]) ) & "-Q" & Text.
#"Insert Day Name" = Table.AddColumn(#"Add Year-Quarter", "Day Name", each Text.Proper( Date.DayOfWeekName([Date], Culture
#"Insert Day Name Short" = Table.AddColumn( #"Insert Day Name", "Day Name Short", each Text.Proper( Date.ToText( [Date], "
#"Insert Day of Month Number" = Table.AddColumn(#"Insert Day Name Short", "Day of Month Number", each Date.Day([Date]), In
#"Insert Day of Week" = Table.AddColumn(#"Insert Day of Month Number", "Day of Week Number", each Date.DayOfWeek([Date]),
#"Insert Day of Year" = Table.AddColumn(#"Insert Day of Week", "Day of Year Number", each Date.DayOfYear([Date]), Int64.Ty
#"Add Day Offset" = Table.AddColumn(#"Insert Day of Year", "Day Offset", each Number.From( Date.From( Today ) - [Date] ) ,
#"Add Week Offset" = Table.AddColumn(#"Add Day Offset", "Week Offset", each ( Number.From( Date.StartOfWeek( [Date], Day.M
#"Add Month Offset" = Table.AddColumn(#"Add Week Offset", "Month Offset", each ([Year] - Date.Year( Today ) ) *12 + ( [Mon
#"Add Quarter Offset" = Table.AddColumn(#"Add Month Offset", "Quarter Offset", each ( [Year] - Date.Year(Today) ) * 4 + Da
#"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
#"Insert Is Weekday" = Table.AddColumn(#"Insert Is Weekend", "Is Weekday", each if Date.DayOfWeek([Date], Day.Monday ) < 5
#"Rename Columns to Language" = Table.RenameColumns(#"Insert Is Weekday", List.Zip( { List.Buffer( TranslateColumnNames[En
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.

···

Tip

Still curious for more? You may want to learn how to create an ISO Week and ISO Year column.

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

Date Table Series

Foundations
Creating a (Dynamic) Date Table
Build a Calendar that supports Multiple Languages
Create a Fiscal Calendar

Advanced Topics
Create ISO Week and Year Column
Return Nth Business Day From Date
Compute Working Days Between Dates
Creating Ordinal Numbers (1st, 2nd, 3rd etc.)
Create Default Slicer Value of Current Month

Julian Dates
Creating Julian Day Numbers
Converting between Julian and Gregorian Dates

Calendar Conditionals Data Types Date Functions List Functions Metadata Table Functions

Share on:

Creating Table Values in Power Master List.Accumulate in Power

Query M (40+ Examples) Query (Step-by-Step Guide)

Latest from my blog

Powe r Q u e r y
Powe r Q u e r y
Mastering M: Which Power
SWITCH Function (Multiple If
Query Functions to Learn and
Statements) in Power Query
When
A p r i l 1 6, 2 0 24
A p r i l 2 3, 2 0 24

Powe r Q u e r y Powe r Q u e r y

Perform Join Based on a Date Remove Milliseconds from

Range in Power Query Time Values in Power Query
A p r i l 8, 2 0 24 A p r i l 4, 2 0 24

Powe r Q u e r y Powe r Q u e r y

Enumerations in Power Query The Definitive Guide to Power

M Query M
A p r i l 2 , 2 0 24 A p r i l 2 , 2 0 24

9 Comments Leave a Reply

Autofan1985
October 26, 2022 at 11:31 AM

Great videos and blog, really helpful! Thanks for that! But the dynamic languages made
me think about something else as well:

Are there also ways to make the first day of your week dynamic? It should be great to
be able use the same calendar like with the different languages you showed, but then
also being able to change the first day of the week between (most commonly) sunday
and monday.

Reply

Rick de Groot
December 2, 2022 at 8:44 AM

Hi!

That should be possible. If you use the build in function of Date.StartOfWeek the
second parameter should get you going:

Date.StartOfWeek(#datetime(2022, 12, 2, 8, 43, 00), Day.Monday)

The second parameter you can then change to any other day of the week. For ISO
weeks you could also try something. But I’m not sure the ISO standard allows for
starting at different days of the week..

Reply

Autofan1985
December 8, 2022 at 2:00 PM

Hi,

Thanks for the answer! That’s indeed the function I’m using now in several
colums (week nr, day of week, etc) but when I try to use a variable in these (like
CalenderLanguage in this article) for the Day.* part I get back errors instead of a
working function.

Reply

Rick de Groot
January 17, 2023 at 7:03 PM

I believe you can make it a variable. But instead of referencing Day.* you
should probably reference the entire Day.Monday, Day.Tuesday etc. Else
Power Query won’t recognize it as element of a function.

Reply

azma
July 15, 2022 at 7:05 PM

what if i want to add another third language. how to be in such a case?

Reply

Rick de Groot
July 17, 2022 at 5:57 PM

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

azma
July 15, 2022 at 6:59 PM

Thanks!

Reply

azma
July 14, 2022 at 7:06 PM

Hello! can i get an example file from you

Reply

Rick de Groot
July 15, 2022 at 9:16 AM

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

Name *

Email *
Save my name, email, and website in this browser for the next time I comment.

POST COMMENT

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About BI Gorilla Latest Posts Newsletter

BI Gorilla is a blog about Mastering M: Which Power Query Functions to Learn and Free your mind, automate
DAX, Power Query and When your data cleaning.
Power BI. It was founded in SWITCH Function (Multiple If Statements) in Power Query
2018 by Rick de Groot with Perform Join Based on a Date Range in Power Query Your email
the goal to provide easy to Remove Milliseconds from Time Values in Power Query
understand resources to help Enumerations in Power Query M GET STARTED

you advance.

M Language Function Reference

You can now find everything you need to know on the Power Query
M language (syntax, examples, articles etc.) at
https://powerquery.how/.

2024 BI Gorilla. Powered by Rocket.net, FlyingPress • Built on theme GeneratePress Blog Contact Privacy Policy