No video :(

Create a Date Table in Power Pivot or Power BI - part 2 of the Date Table Series

Подписаться 6 тыс.

Просмотров 6 тыс.

50% 1

In this Video (Part 2 of the Date Table Series) you are going to learn how to create a Date Table (with "DAX") in Power Pivot and in Power BI. You will notice that whereas the final Date Table is "created" by DAX, all you need to know are a couple of commands.
This tutorial was made using Excel 365 and Power BI Desktop (June 2021). Keep in mind that for Excel 2010 Power Pivot is a separate AddInn you have to install.
Links to other parts of the Date Table Series:
Part 1: What is a Date (Calendar) Table and how to create one in Excel: bit.ly/2TTuABk
Part 2: Create a Date Table in Power Pivot or Power BI: this video
Part 3: Create a Date Table with Power Query in Excel or Power BI:bit.ly/3xuxDh8
Part 4: Extending the Date Table - Why you NEED to do it: bit.ly/2WRy20p
Part 5: Extending the Date Table with DAX in Power Pivot or Power BI: bit.ly/3CsQm0q
Part 6: Extending the Date Table with Power Query in Excel or Power BI: bit.ly/3hGQKj3
Chapters:
0:00 Excel Olympics Animation
0:08 Introduction to the Video
1:01 Creating a Date Table in Power Pivot
8:08 Creating a Date Table in Power BI
13:35 Spoilers for next videos
14:11 Outro
Gear Used:
US Links
Camera: Canon EOS R6 - amzn.to/3e9zxML
Microphone: AKG C3000 (Analog Mic) - amzn.to/3tYd37w
Audio converter: Zoom UAC-2 - amzn.to/3dgM8wS
Laptop: Dell XPS 15 - amzn.to/3w8CyoK
Lights: Elgato Key Light - amzn.to/3rurte7
EU Links
Camera: Canon EOS R6 - amzn.to/3tubwGy
Microphone: AKG C3000 (Analog Mic) - amzn.to/32pi2m0
Audio converter: Zoom UAC-2 - amzn.to/3amDRXP
Laptop: Dell XPS 15 - amzn.to/3effMmT
Lights: Elgato Key Light - amzn.to/3vcs6v9
Green Screen (visible in bloopers): amzn.to/3ajUm6Y
Recorded With: Camtasia - www.techsmith....
Subscribe for more Excel tutorials on the Excel Olympics RU-vid Channel: bit.ly/EOYTsub
Subscribe for the Excel Olympics Newsletter: bit.ly/3w3rkjK
Disclaimer: Links included in this description might be affiliate links. If you purchase a product or service with the links that I provide, I may receive a small commission with NO additional charge to you! I appreciate your support!
#Excel #ExcelOlympics #gasperkamensek

Опубликовано:

17 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 7

@wayneedmondson1065 3 года назад

Hi Gasper. Thanks for the video. Looking forward to the Power Query version. Thumbs up!!

@ExcelOlympics 3 года назад

Thanks Wayne. The Power Query version is coming up next Tuesday.

@wayneedmondson1065 3 года назад

@@ExcelOlympics Great! In anticipation, I came up with the following generic example of how to generate a dynamic date table from dates in an existing table of data: 1. Load Table1 which includes a column with dates named "Date". 2. From the Date column of Table1, extract the min and max dates into two new queries with = List.Min(Table1[Date]) and = List.Max(Table1[Date]); I named them MinDate and MaxDate. 3. Start a new blank query named Calendar; Source step is: = {Number.From(MinDate).. Number.From(MaxDate)}; convert to table; change type to Date. You now have a dynamic date table starting from the earliest date and ending with the latest date which will adjust with new data. Bonus.. do basically the same procedure, except calculate the Start of Year and End of Year for MinDate and MaxDate to produce a date table from Jan 1 of the earliest year and Dec 31 of the latest year. Lots of ways to do this. Mine is a combo of some M Code and some UI steps. Looking forward to your video which I'm sure will provide some new insights. Fun to try and solve problems before watching the solution :)) Thumbs up!!

@ExcelOlympics 3 года назад

The video will be a kind of medley of all your suggestions plus a bit of append due to multiple date columns from multiple tables. But you have 85% covered in this comment 😀

@kierondarcy855 27 дней назад

Hi Gasper, love your work. I'm a beginner, and I'm trying to follow your steps which you breakdown throughout the series. I'm having difficulty adding a column in bulk (12:31 in the above video). I have copied everything, cell by cell, formatting and it all works to this point. I enter: 1 calendar = ADDCOLUMNS ( CALENDAR("1/1/2017","31/12/2022"), but when I hit return, I receive an error notification starting ! Too few arguments............' what am I doing wrong?

@rubenmunozverdu7528 2 года назад

What if you add the third table (or the column from the first table) after you created the date table? Would that be a problem? Would the date table update automatically?

@ExcelOlympics 2 года назад

Hi Ruben. Thank you for that question. In this instance it would. The Excel and Power Query calendars would need to be made aware of the new table but the Power Pivot Calendar would include it automagically 😀.