Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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" )
)
A calendar with only dates:
CalendarTable = CALENDAR("01/01/2015";"31/12/2015")
@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/
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 'Column1' of the table wasn't found.
Details:
Column1
No way to getting error, pls share your power query
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...
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
You can use DAX to generate a calendar table based on CALENDAR() function. Just new a table and input DAX below:
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
In Import
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.
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |