Forum Discussion

iLikeAzureSQL's avatar
iLikeAzureSQL
Helper I
10 years ago
Solved

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, i...
Need Help
  • iLikeAzureSQL's avatar
    iLikeAzureSQL
    10 years ago

    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" )
    )

iLikeAzureSQL's avatar
iLikeAzureSQL
Helper I
10 years ago

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

Baskar's avatar
Baskar
Resident Rockstar
10 years ago

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...

  • iLikeAzureSQL's avatar
    iLikeAzureSQL
    Helper I
    10 years ago

    Getting this error:

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

    • Baskar's avatar
      Baskar
      Resident Rockstar
      10 years ago

      No way to getting error, pls share your power query