Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

This post describes how to create a dynamic date table using Power Query’s M language. We will start from scratch. You’ll then learn how to generate a date series and create columns using the user interface.

Once we’ve got the basics down, we’ll dive into some more advanced techniques, like creating custom columns and making the calendar table start or end dynamically.

Oh, and if you’re into code, make sure you stick around until the end. I’ve got a free date table script that you can download and use for yourself!

Table of contents

Purpose of a Date Table

So why use a date table in Power BI? Data models often require a way to analyze data over time. This is where a date table comes in. A date table is a specialized table that contains a continuous and comprehensive range of dates, which can be used to perform time-based analysis.

Each row in the table includes information about the date, such as the day, month, year, quarter, and even fiscal periods. The table can also include other relevant information, such as holidays or business days.

The primary purpose of a date table is to provide a way to group and filter data based on dates. With a date table, you can create time-based calculations and analysis, such as year-to-date (YTD) calculations, comparison of data between two different periods, and running totals. Without a date table, it can be challenging to perform such analyses.

Create Date Table or Calendar in Power Query M (Complete Guide)

Add Parameters

Let’s talk about how to create parameters for your date table. This will help you make your calendar table more flexible and dynamic. To get started, open Power Query and go to Manage Parameters.

Once you’re there, you’ll want to create a parameter for both the start and end dates of your calendar table. Click on the “New” button and then add the following information:

  1. For the start date, enter the name “StartDate“, choose the type “Date“, and set the current value to “01-01-2021“.
  2. For the end date, enter the name “EndDate“, choose the type “Date“, and set the current value to “31-12-2022“.
0.5 Manage Parameters

Now, you have two parameters that you can reference in the following chapters. These parameters allow you to easily change the start and end dates of your calendar table without having to modify your queries every time.

With just a few clicks, you can update your calendar table to include data for any date range you choose. Pretty cool, right?

0. Two parameters for calendar

Create a List of Dates

4 Ways to Create Date Column for a Calendar in Power Query

When building your Power BI calendar table, the first thing you need is a list of dates to use as the foundation. DAX time intelligence functions require a consecutive list of dates, so it’s important to avoid any gaps in your date dimension. But how can you generate this list of dates?

To start, create a blank query by right-clicking in the queries pane and selecting New Query -> Blank Query. This will give you a clean slate to work with.

From here, the next step is to create a list of consecutive dates using M, which will serve as the basis for your date column. If you’re new to working with lists, you can check out the complete guide to lists in Power Query for more information.

There are several ways to create a list of dates, as outlined below.

List.Dates

The easiest way to create a date table in Power Query is:

  • use the List.Dates function and enter a start date.
  • indicate the number of steps in your calendar.
  • specify a single day as duration

An example is the following expression that creates a list with 5 consecutive dates. To see this in action, add the below code in the formula bar of your blank query:

= List.Dates(                      // Creates a list of dates
     #date( 2021, 10, 15 ),        // Starting from Oct 15th 2021
     5,                            // Creating 5 steps
     #duration( 1, 0, 0, 0 )       // Of 1 Day
 )
1. Create a list of dates with List.Dates

You can see the three arguments of the function. A start date (as date), the number of intervals to add (as number) and the size of each interval (as duration).

Tip

If the #duration() syntax is new to you, it is good to know it takes 4 arguments as input: days, hours, minutes and seconds. The above formula, therefore, increments with 1-day intervals. Changing the duration from 1 day to 2 days, creates a list that increments with 2 days per step.

Next, you can try to make this formula more dynamic. Instead of hardcoding a start date, adjust the original formula and include the parameter called StartDate:

= List.Dates(
     StartDate,               // Uses start date parameter
     5, 
     #duration( 1, 0, 0, 0 )
  )

To also make the total amount of intervals dynamic, you can retrieve the number of days between the end date and the start date.

Function argument 2 requires a number as input, and one way to the difference in days is by using the Duration.Days function. Just make sure to add +1 at the end, or your date table will miss a day.

= List.Dates(
     StartDate, 
     Duration.Days( EndDate - StartDate ) + 1, // Number of steps
     #duration( 1, 0, 0, 0 )
  )

The code now generates a date series formatted as a list. The last step is to convert this list to a table for the start of your calendar table. You can do that by selecting To Table in the Transform tab. This generates the code:

= Table.FromList(
     Source, 
     Splitter.SplitByNothing(), 
     null, 
     null, 
     ExtraValues.Error
  )

To save yourself the step of renaming the new column (Column1) and defining the data type later, you can make an adjustment to the previous formula. You can do this by defining the table syntax in the argument. Lastly, arguments 4 and 5 are optional, you can remove them.

= Table.FromList(
     Source, 
     Splitter.SplitByNothing(), 
     type table[ Date = Date.Type ], // Adds Column Name and Data Type
  )
3.5 Change data type and rename

Autofill Dates in List

Another way to create a consecutive series of dates is to use the auto-fill functionality for lists in Power Query. To see how this works, inspect the following examples that generate a list:

= { 1..10 }      // Creates list: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
= { "a" .. "k" } // Create list: a, b, c, d, e, f, g, h, i, j, k

You can use this functionality to create a list that starts at the StartDate and reaches up to the EndDate.

Yet because this functionality does not work with dates, you can first transform the dates to their corresponding numbers using the function Number.From. The following is a completely valid syntax:

= { Number.From( StartDate ) .. Number.From( EndDate ) }
3. Autofill dates in list

The number 44197 here relates to the date January 1st 2021. Once you have this list, you can transform it into a table, as shown in method 1.

You are now left with a column with numbers. The last thing to do is to change the column Type to Date. And with that, your calendar table gets a list of consecutive dates.

List.Numbers

The third method creates a list of dates in a similar way as method 2. Yet, instead of using the autofill functionality, you use the List.Numbers function.

= List.Numbers( 
     Number.From( StartDate ), 
     Duration.Days( EndDate - StartDate   ) + 1 
  )

The first argument shows the starting number corresponding to the StartDate. The second argument provides the number of increments to add to the list in steps of 1.

Turn that list into a table and transform the column type to date.

List.Generate

The List.Generate function is effective for creating lists, which is a great starting point for a calendar table.

An effective way to create a Calendar table is:

  1. Create a blank query.
  2. Type = List.Generate( () => StartDate, each _ <= EndDate, each Date.AddDays(_, 1 ) )
  3. Convert the list of dates into a table.
  4. Rename the column to “Date” and the data type to ‘Date‘.
  5. Voila, now you have your very own Date Table.
= List.Generate( 
     () => StartDate,           // initial value
     each _ <= EndDate,         // run until EndDate
     each Date.AddDays(_, 1 )   // increase 1 day for each step
  )

Let’s break down the formula a little bit:

  • The first argument shows the initial value, which is the StartDate.
  • The second argument instructs List.Generate to perform the function of the third argument for as long as the number is smaller or equal to the EndDate.
  • The third argument provides the function to perform. In this case, it instructs to increment each step generated by 1 day.

Important

It’s important to understand that the List.Generate function is complex and powerful, and deserves its own article. But, the above should give you a basic understanding of how it works.

Read more: Understanding List.Generate in Power Query with Easy Examples.

Remember, there are four methods to create your calendar table, so feel free to pick your favourite. They all help in building the foundation of your date table, the consecutive list of dates.

Adding Columns to Date Table

The next step is to add relevant columns to your calendar table. You can easily add most columns through the user interface, which is what I try to teach people as a first step.

Using the User-Interface

Navigate to the Add Column tab and make sure the Dates column is selected. Then click on the Date icon. The button will only be available when working with a DateTime or a Date column. After clicking, the below dropdown provides you with several options.

4. Add Date Columns

And this is where Power Query makes it really easy for you to add new columns. For example, clicking Month and selecting Name of Month, creates a column with the Month Name.

To add another column, repeat the process by selecting the date column again and then choosing another column for your date dimension.

You can have a look at the below table to inspect the available options. To make things easy for you, I have added sample results in the comments behind each function.

Date.From( DateTime.LocalNow() ) - [Dates] // Adds Aging Duration (287.00:00:00)
 
Date.Year( [Dates] )                       // Adds Year Number (2021, 2022)
Date.StartOfYear( [Dates] )                // Adds Start of Year Date (01-01-2021)
Date.EndOfYear( [Dates] )                  // Adds Start of Year Date (31-12-2021)
 
Date.Month( [Dates] )                      // Adds Month Number (1-12)
Date.MonthName( [Dates] )                  // Adds Month Name (January - December)
Date.StartOfMonth( [Dates] )               // Adds Month Start Date (01-02-2021)
Date.EndOfMonth( [Dates] )                 // Adds Month End Date (28-02-2021)
Date.DaysInMonth( [Dates] )                // Adds Days in Month (31, 28, 30 )
 
Date.QuarterOfYear( [Dates] )              // Adds Quarter Number (1-4)
Date.StartOfQuarter( [Dates] )             // Adds Quarter Start Date (01-04-2021)
Date.EndOfQuarter( [Dates] )               // Adds Quarter End Date (31-03-2021)
 
Date.WeekOfYear( [Dates] )                 // Adds Week Number of Year (1-53)
Date.WeekOfMonth( [Dates] )                // Adds Week Number of Month (1-5 )
Date.StartOfWeek( [Dates] )                // Adds Week Start Date (18-01-2021)
Date.EndOfWeek( [Dates] )                  // Adds Week End Date (24-01-2021)
Date.Day( [Dates] )                        // Adds Day of Month (1-31 )
Date.DayOfWeek( [Dates] )                  // Adds Day of Week (1-7)
Date.DayOfYear( [Dates] )                  // Adds Day of Year (1-365)
Date.DayOfWeekName( [Dates] )              // Adds Day Name (Monday - Sunday)

Common Custom Columns

The columns you have seen so far probably meet 90% of your needs. The other missing 10% requires more customization from your side.

This chapter deals with the most important custom date columns. The code in the below examples you can add by going to Add Column -> Custom Column. You can then paste the code in the Custom Column Formula box.

The first column I recommend adding is the Month Name Short column. This is an abbreviated version of the month’s name. So January, February, and March turn into Jan, Feb, Mar.

Note

It may seem as if this is always the first three letters of the Month’s Name. Yet, depending on the language you use, this is not always true. In Dutch, for example, we have Januari, Februari, and Maart, which we turn into Jan, Feb, and Mrt.

As you can see, March does not transform nicely with the extraction of the first three letters. There’s a hack for this that works in all languages. You can use the Date.ToText function. The second argument of this function allows you to provide all kinds of formatting options that you may know of other applications like Excel.

Caution

Depending on the settings of your Power BI file, the result may be in your desired formatting. But what happens when you open your file on a different machine that perhaps has a different default language? It may actually change the output of your queries.

To build a more robust solution, you can force a culture in the third optional argument of the Date.ToText function. For a list of available culture codes, you can go here. For Dutch, this is “nl-NL”, and the general English one is “en-EN”. Once you find your desired culture, you can add it like:

= Date.ToText( [Dates] , "MMM" )         // Short month names (Jan, Feb, Mar, Apr) 
= Date.ToText( [Dates], "MMM", "nl-NL" ) // Dutch culture, returns ( jan, feb, mrt )
= Date.ToText( [Dates], "MMM", "en-EN" ) // English culture, returns ( Jan, Feb, Mar )

Knowing this, you can even generate a calendar table that adjusts to the user’s language. You could, for example, have a parameter with the desired language, and adjust the culture codes accordingly.

If you take it a step further, you could even change the column names to reflect the selected language. This requires some more attention and is a topic worth a separate blog post.

Another common column to add to your calendar table is the Year-Month column. And you can use different formats here. Some examples are:

= Date.ToText( [Dates], "yyyy-MM" )  // (2021-01 - 2021-12) 
= Date.ToText( [Dates], "MMM yyyy" ) // (Jan 2021 - Dec 2021) 
= Date.ToText( [Dates], "yyyyMM" )   // (202101 - 202112) 

For quarter columns in your date table, the below formatting is common.

"Q" & Text.From( Date.QuarterOfYear( [Dates]) )  // (Q1 - Q4)

= Text.From( Date.Year( [Dates] ) ) &"-Q"        // (2021-Q1 - 2021-Q4)
      &  Text.From( Date.QuarterOfYear( [Dates] ) ) 

Other columns frequently included in the date dimension indicate whether a day is in the weekend or the weekday. One way to write this is:

= if Date.DayOfWeek( [Dates] ) >= 5 then 1 else 0 // Adds flag: is Weekend
= if Date.DayOfWeek( [Dates] ) <  5 then 1 else 0 // Adds flag: is Weekday

If you have text values with funky date formats, make sure to check out how to extract dates from text values.

Advanced Custom Columns

Next to the just mentioned columns, there are some more advanced custom columns. These topics deserve their own blog post and you can find them below:

Creating a Dynamic Date Table

So far, we have used parameters to determine both the start and end date of the calendar. For some, this meets their needs.

Yet there are cases where you only want to see dates that meet certain criteria. This chapter shows two ways on how to make the calendar table truly dynamic.

Dates up to Today

Earlier, we used the Manage Parameters box to add a hardcoded end date. Instead of having this fixed end date, you can easily end your calendar table on today’s date.

If this does not bring any complications with future dates (e.g. budgets, forecast, etc.), you can replace the parameter by the date of today in the following way.

Start by creating a New Query, and select Blank Query. Your goal is to retrieve the current date. Power Query knows the function DateTime.LocalNow that retrieves the current DateTime value.

= DateTime.LocalNow() 

Since we need a Date value, you can include Date.From:

= Date.From( 
     DateTime.LocalNow() 
 )

The result of the formula is the current Date on your machine. You can rename this query to DateToday. Now, instead of referencing the EndDate parameter, you replace that value with DateToday. With this adjustment, your Calendar table will always run up to today.

Tip

To make your query more robust, you can also generate the date of today that respects daylight savings. This ensures the time is always correct, regardless of the location where you query refreshes.

Dates up to Max Sales Date

Another common approach is to maximize the calendar table to the data with the latest sales. This approach is very similar to the previous one.

Imagine you have a table called Sales that contains a column with OrderDate. To create your MaxSalesDate parameter, you first create a blank query and rename it to MaxSalesDate. Next, you retrieve the maximum date from the Sales table.

Referencing a column name in a table results in a list. To get the latest sales date we can therefore use the List.Max function:

= List.Max( Sales[Date] )

With this parameter in place, you can now reference the MaxSalesDate instead of the EndDate Parameter.

Date Table Script

So far, we have seen a wide range of possible columns. It’s likely you only need a selection or have some other wishes.

To help you set up the foundation, you can copy below date table script below to generate your date table in Power Query’s M language. Starting from this template, you can easily adjust it to fit your needs.

let
    Today = Date.From( DateTime.LocalNow() ),
    StartDate = #date(2022, 1, 1),
    EndDate = Date.EndOfYear( Today ),
    #"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 "Current" 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 Date.ToText( [Date], "MMM yyyy"), type text),
    #"Insert Month Number" = Table.AddColumn(#"Insert Month-Year", "Month Of Year", each Date.Month([Date]), Int64.Type),
    #"Insert Month Name" = Table.AddColumn(#"Insert Month Number", "Month Name", each Date.MonthName([Date], "EN-us"), type text),
    #"Insert Month Name Short" = Table.AddColumn(#"Insert Month Name", "Month Name Short", each Date.ToText( [Date] , "MMM", "EN-us" ), 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 Of Year] then "Current" 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 ),
    #"Add ISO Year" = Table.AddColumn(#"Add ISO Week", "ISO Year", each Date.Year(  Date.AddDays( [Date], 26 - [ISO Weeknumber] ) ), 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 Date.DayOfWeekName([Date], "EN-us" ), type text),
    #"Insert Day Name Short" = Table.AddColumn( #"Insert Day Name", "Day Name Short", each Date.ToText( [Date], "ddd", "EN-us" ), type text),
    #"Insert Day of Month Number" = Table.AddColumn(#"Insert Day Name Short", "Day of Month Number", each Date.Day([Date]), Int64.Type),
    // Day.Monday indicates the week starts on Monday. Change this in case you want the week to start on a different date. 
    #"Insert Day of Week" = Table.AddColumn(#"Insert Day of Month Number", "Day of Week Number", each Date.DayOfWeek( [Date], Day.Monday ), 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 Duration.Days( Date.StartOfWeek( [Date], Day.Monday ) - 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 Of Year] - 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] ) >= 5 then 1 else 0, Int64.Type ),
    #"Insert Is Weekday" = Table.AddColumn(#"Insert Is Weekend", "Is Weekday", each if Date.DayOfWeek( [Date] ) < 5 then 1 else 0, Int64.Type )
in
    #"Insert Is Weekday"

Conclusion

So there you have it – a step-by-step guide to creating a dynamic date table using Power Query’s M language. We covered everything from generating a date series to creating custom columns and making the calendar table dynamic.

But don’t stop there! If you want to take your Power BI calendar table to the next level, you can add a flexible calendar language for column names and values.

And if you really want to delve in, you can also explore creating working day numbers that exclude weekends and holidays, or even returning the nth business day from a date.

With these advanced techniques, you’ll have a calendar table that’s truly dynamic and flexible. So go ahead and build amazing Power BI models with confidence, knowing that you have the tools and knowledge to create a powerful and effective date table.

Enjoy Power Query!

Share on:
  1. Hi, you have created two such solutions:
    “ISO Week”
    “ISO Year”
    But what about: “ISO DAY OF WEEK”? Something like WEEKDAY in DAX? Where I can choose to count the days of the week from 1 to 7. And one is Monday.
    Would you help here?

    Reply
    • Jacob!

      Good question. You can use the build in function for that which is; Date.DayOfWeek. The second argument of the function allows you to indicate the start of the week. For example:

      Date.DayOfWeek( [Date], Day.Sunday ).

      The function returns values 0 to 6 for the workdays of the week. If you want it to show 1-7 simply add +1 to the function. Enjoy!

      Reply
  2. Hello Rick,

    Thanks for this well explained and overall complete post. Most blogs give you only one proposed solution for a specific problem.

    I created a calender table this way:

    let
    Startdate = #date(2020,1,1),
    Enddate = #date(2021,4,30),
    #"Date list" = List.Generate(
    () => Number.From(Startdate),
    each _ <= Number.From(Enddate),
    each _ + 1),
    #"List to table" = Table.FromList(#"Date list", Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"List to table",{{"Date", type date}}),
    #"Duplicate columns" = Table.DuplicateColumn(
    Table.DuplicateColumn(
    Table.DuplicateColumn(
    Table.DuplicateColumn(
    Table.DuplicateColumn(
    Table.DuplicateColumn(
    Table.DuplicateColumn(#"Changed Type", "Date", "Year"),
    "Date", "Quarter"),
    "Date", "Month"),
    "Date", "Day"),
    "Date", "Weeknumber"),
    "Date", "Monthname"),
    "Date", "Dayname"),
    #"Full table creation" = Table.TransformColumns(#"Duplicate columns", {{"Year", Date.Year}, {"Quarter", Date.QuarterOfYear}, {"Month", Date.Month}, {"Day", Date.Day}, {"Weeknumber", Date.WeekOfYear}, {"Monthname", Date.MonthName}, {"Dayname", Date.DayOfWeekName}})
    in
    #"Full table creation"

    But in the last line where I do Table.TransformColumns I can’t manage to write the short month name. I have tried with Date.ToText( [Date] , “MMM”, “EN-us” ) but PQ doesn’t accept the [Date]. Any ideas?
    thx

    Reply
    • Hi Stijn,

      For your short monthname you can use:

      {"Dayname", each Date.ToText(_, "MMM") }

      Just make sure to add the word ‘each’ before the statement, and the _ to reference the column.

      Reply
  3. One comment – and One question

    I’m using for generating Date Table something like:

    let
        Columns = {"Year", "Month", "Day", "MonthName", "KuuNimi"},
        Allikas = List.Dates( #date(2022,1,1), 365, #duration( 1, 0, 0, 0 ) ),
        #"Teisendatud tabeliks" = Table.FromList(Allikas, Splitter.SplitByNothing(), type table[Date = Date.Type ], null, ExtraValues.Error),
        #"Lisatud kohandatud" = Table.AddColumn(#"Teisendatud tabeliks", "Kohandatud", each Record.FromList(
    {
    Date.Year([Date]),
    Date.Month([Date]),
    Date.Day([Date]),
    Date.ToText([Date], "MMMM", "en-us"),
    Date.ToText([Date], "MMMM", "et-ee")
    },
    Columns
    )),
        #"Laiendatud Kohandatud" = Table.ExpandRecordColumn(#"Lisatud kohandatud", "Kohandatud", Columns, Columns),
        #"Muudetud tüüp" = Table.TransformColumnTypes(#"Laiendatud Kohandatud",{{"Month", Int64.Type}, {"Day", Int64.Type}, {"MonthName", type text}, {"KuuNimi", type text}})
    in
        #"Muudetud tüüp"

    So I simply minimize the steps in my Query and create several columns once

    BUT – do You know any way to expand record with exact type to precvent TransformColumnTypes

    Reply
  4. Pingback: Import Multiple Files from a Folder with Power Query - Gorilla BI
  5. Pingback: Create Date Table with Dynamic Language in Power Query - Gorilla BI
  6. I had to go “MM” on the step

    = Table.AddColumn(
      #"Converted to table", 
      "Date Integer", 
      each Date.ToText([Date], "yyyyMMdd"), 
      Int64.Type
    )

    But maybe thats just my regional settings. mm was minutes (gave error) for me.

    Reply
    • Helge. For me it didn’t throw an error, but the dateinteger just showed 00 for month. I changed it to MM in line with what you suggested.

      Thanks for pointing that out!

      Reply
  7. Pingback: Lists in Power Query - The Complete Guide - Gorilla BI

Leave a comment

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