Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iLikeAzureSQL
Helper I
Helper I

Date Domension with DirectQuery connection

Hello all,

I need to create a Date Dimension table in my model while I'm usting DirectQuery to Azure DB.

Creating a new table is disabled when I use DirectQuery. What are my options?

 

Thanks,

iLikeAzureSQL

1 ACCEPTED SOLUTION

I had to make minor changes with quotations to have it work:

Date =
ADDCOLUMNS (
CALENDAR ( "1-jan-2000", "31-dec-2025" ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "dddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

View solution in original post

16 REPLIES 16
Vvelarde
Community Champion
Community Champion

A calendar with only dates:

 

CalendarTable = CALENDAR("01/01/2015";"31/12/2015")

 

 

 




Lima - Peru

@iLikeAzureSQL Build it in your Azure SQL DB. There are a bunch of blogs out there that have the code and describe how to do this. Just as an example: https://sqldusty.com/2012/04/12/create-date-dimension-script/

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Baskar
Resident Rockstar
Resident Rockstar

 

 

Use the below Power Query to create Date Master then use it, Try it

 

let
Source = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - 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])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
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", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & "'" & Text.Range(Number.ToText([Year]),2,2)),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekStarting = Table.AddColumn(InsertWeekEnding, "WeekStart", each Date.StartOfWeek([Date]), type date)
in
InsertWeekStarting,
#"Invoked FunctionSource" = Source(#date(2016, 1, 1), #date(2016, 12, 31), null),
#"Changed Type" = Table.TransformColumnTypes(#"Invoked FunctionSource",{{"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayInWeek", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
#"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Date - Copy", Date.WeekOfYear}}),
#"Renamed Columns" = Table.RenameColumns(#"Calculated Week of Year",{{"Date - Copy", "WeekofMonth"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"DateInt", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Sorting Order", each [Year]*100 + [MonthOfYear]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each true),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Sorting Order", Int64.Type}})
in
#"Changed Type2"

Where should I enter Power Query ? I'm not finiding it !

1. Click Edit Queries window

2.  Choose Blank Query in Get Query button and then click ok.

3. click "Advanced Editor" and totally replace the power which i given.

 

 

now youe date master ready to use...

Getting this error:

Expression.Error: The column 'Column​1' of the table wasn't found.
Details:
    Column​1

No way to getting error, pls share your power query

 

@Baskar Here is what it looks like

 

3.jpg4.jpg

Hi,

 

Its working for me without any error prompt.

 

Do one thing again copy the entire query which i given, delete all the power query in that table and paste the new query and try...

 

ankitpatira
Community Champion
Community Champion

@iLikeAzureSQL Go to Query Editor and click Enter Data which will let you create a new table.

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

Thank you for your reply, but using that will only give me option to manulay enter the data. I intend to use DAX to create the DATE Dioomension table

@iLikeAzureSQL

 

You can use DAX to generate a calendar table based on CALENDAR() function. Just new a table and input DAX below:

 

Capture232.PNG

 

Date =
ADDCOLUMNS (
CALENDAR ( “1-jan-2000”; “31-dec-2025” );
“DateAsInteger”; FORMAT ( [Date]; “YYYYMMDD” );
“Year”; YEAR ( [Date] );
“Monthnumber”; FORMAT ( [Date]; “MM” );
“YearMonthnumber”; FORMAT ( [Date]; “YYYY/MM” );
“YearMonthShort”; FORMAT ( [Date]; “YYYY/mmm” );
“MonthNameShort”; FORMAT ( [Date]; “mmm” );
“MonthNameLong”; FORMAT ( [Date]; “mmmm” );
“DayOfWeekNumber”; WEEKDAY ( [Date] );
“DayOfWeek”; FORMAT ( [Date]; “dddd” );
“DayOfWeekShort”; FORMAT ( [Date]; “dddd” );
“Quarter”; “Q” & FORMAT ( [Date]; “Q” );
“YearQuarter”; FORMAT ( [Date]; “YYYY” ) & “/Q” & FORMAT ( [Date]; “Q” )
)

Reference:
HOW TO CREATE A DATE TABLE IN POWER BI IN 2 SIMPLE STEPS

 

 

Regards,

I had to make minor changes with quotations to have it work:

Date =
ADDCOLUMNS (
CALENDAR ( "1-jan-2000", "31-dec-2025" ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "dddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

I am coming up with this problem as well. I need to add a date table to my Direct Query report.

The issue is that the answers so far seem a bit confusing. I can't add a table or use DAX blank query to add the table.

In Direct Query Mode
Direct Query New Table.png

 

In Import

New Table Import Mode.png

The option to add a new table or use a blank query is all possible and I have used before in Import mode however in Direct Query mode it is not avaliable.

 

Adding any tables or custom columns just prompts me to change the report from Direct Query to Import mode.

Help on this would be greatly appreciated.

Cheers. ED.

 

 

I am getting the same issue.

 

I cannot add blank query or a new table to my Direct Query!

 

As I understand it, DirectQuery is most appropriate to use when connecting to a source that has a fairly complete data model. Your date dimension should come from your data source just like everything else. If you cannot create a date dimension in your data source or you want to create a custom date dimension within Power BI's query editor you will need to use import instead of DirectQuery.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.