Ultimate Beginners Guide to Power BI 2021 - Creating A Date Table (1.4)

Here's our Ultimate Power BI tutorial that will bring you from beginner to Power BI pro. This Power BI training will teach you how to work on your Power BI desktop to create your first compelling Power BI dashboard.

Follow along to the beginner's guide Power BI tutorials by using the demo data available for download. Enjoy!

Details of what you will learn during these Power BI course modules & tutorials
Learn - how to use Power BI as an analytical engine, turning raw data into unmatched insights
Save - mountains of time by learning how to automate analysis and reporting
Learn - how to look at raw data and then apply Power BI and DAX to find answers
Learn - how to find the insights that make a difference and add value to decision making
Develop - compelling visualizations that showcase insights that will add value to business discussions and actions

Here are the specifics for the course
Learn how to:
- Develop a best practice Power BI model
- Plan Power BI development before you start
- Query, transform and load data
- Create a data model
- Write DAX measures
- Develop compelling reports and visualisations
- Publish to the Power BI online service
- Create dashboards
- Use the Q&A feature
- Use other online service features

***** Learning Power BI? *****

***** Related Links *****

***** Related Course Modules *****

***** Related Support Forum Posts *****

[EnterpriseDNA]

Learning Power BI?

Related Links

Related Course Modules

Related Support Forum Posts

[matheusbacha]

Here's the table code for those who need it:


let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = - StartDate)),
Source = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList, {{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType, {{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName], 0, 3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber, "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear, {{"QuarternYear", Int64.Type}, {"Week Number", Int64.Type}, {"Year", type text}, {"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then else [ShortYear]))
in
AddFY
in
fnDateTable

[thomasnemeth]

I've been using Power BI for a few years now and really appreciate these videos as a more formal introduction to everything. I'm learning little tips and tricks so far about certain things that I never thought may be better ways of doing things I'm already doing.

But for the life of me... there is no explanation as to why this "date table" is so important. And I've seen many other people use "date tables" for certain things but I've never EVER seen the point or function in them. So I appreciate being shown a nice fun easy way to make a "date table" -- I just have no idea why I ever would make a "date table" ... maybe it'll get used for something neat in a future video... 🤷‍♂️

[pablofonseca]

Great video! I'm loving this classes. I'm watching from Brazil

[juniorjuliuspeternaddin]

hello, Good day, please. I just started learning Power Bi from Enterprise DNA, and the examples are very helpful. However, I have a question about the power Bi date table code. Is that stranded code for all date tables, or do I have to go to New Source, blank query, advanced editor, and type out what I want from your excel sheet?

[csrajaraman]

Thanks for this session. I am using this data. But is there a way to set the end date as the last date of dates?

[toshiehabu]

This is very helpful. I've tried several times to set the FY staring month as April (4), but it doesn't show correctly. In my data table, April as Q2. I've tried the setting doesn't change. I wonder what 's wrong with my date table?

[lisacalhoun]

So helpful! Thanks! My question is, if you change the column name in Query Editor, then later need to refresh the table in Query Editor (so that it picks up new data from the original data source), will the new column names remain after the refresh?

[douglaspiresmartins]

Hi Sr, how are doing? I hope well. About this vid, I have a question. Here in my company we work with a corporate year, have you heard about that? So it starts from 01/09/x1 and ends 31/08/x1, for example Now we are in the corporate year 2019/2020 by 31/08/2020 because in 01/09/2020 will be 2020/2021. Should I make a normal data table starting 01/01/x1 by 31/12/x1 and using languague M with IF statemant to handle this? ( if Date.Month([Date]) >8 then Date.Year([Date]) & Date.Year([Date])+1 and so on...) OR I can elaborate a datatable start from 01/09/x1 by 31/08/x1 Many thanks.

[darrengreenberg]

Could the CALENDAR function also be used on the fact table to create a Date table?

[u]

Hi, how can I get the Date Table and the data sets that you are using to work on myself? Thanks. Can you email them to me? thanks MJ

[harnidh]

Can't get the resources even after registering on your website?

[robertoafane]

How do you sort by Month In Calendar? It sorts by alphabetical order when I add it to my report so the months are all messed up.

[lulufly]

Should I mark the table as Data Table?

[Zunidrap]

Any reason why the master class videos are gone? :(

[jamalq]

Sir, please make tutorial on power bi with python.

[beenishali]

Please can you email me the Date table M code? Appreciate the help

[juniorjuliuspeternaddin]

hello, Good day, please. I just started learning Power Bi from Enterprise DNA, and the examples are very helpful. However, I have a question about the power Bi date table code. Is that stranded code for all date tables, or do I have to go to New Source, blank query, advanced editor, and type out "in code" to get what I want from your excel sheet?