table not appearing in power query/relationships

[Anonymous]

I have an incredibly bizarre situation.  I've created a date table, but it isn't showing up under relationships or in power query.  Any suggestions?

[HotChilli]

how did you create the date table?

[Anonymous]

date = calendarauto()

i don't need it to show up in power query.  i only want to make it the calendar table for time intelligence functions.  but it's not showing up under relationships to do this.

[HotChilli]

OK, so we know it won't show up in power query.

I'm in agreement with @jthomson  the 2nd screenshot is the powerbi screen overlaid with the Power Query screen so we're not looking at the relationships screen.

Sometimes we lose track of tables in the relationships screen because the screen has been zoomed in too much.  Can I suggest hitting the 'Reset Layout' button (bottom right in the relationships screen) so we can eliminate that.

[Anonymous]

there are no scroll bars in the relationships screen, but i went ahead and clicked reset layout.  table still not there.  though i wouldn't expect that bc the table isn't in the list of fields.

[v-kelly-msft]

Hi  @Anonymous ，

As tested here,the calendar table should show in the Model(relatinship) view:

Which version of desktop are you using?

Could you pls upload your .pbix file for test?Remember to remove the confidential information.

Best Regards,
Kelly

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

[jthomson]

If it's a calculated (DAX) date table, I wouldn't expect it to show up in Power Query as it's not a query. No idea on the context of your second image, I don't recognise that screenshot, so I guess that list of queries implies it's from the latest new gadget that I've not turned on. But, again, it's not a query.

[Anonymous]

that second screenshot is the relationships view.  i don't need it to show up in power query, i just need to be able to establish the relationship for time intelligence.

[aj1973]

Hi @Anonymous 

Because it is a Calculated table that you added it in the model and not in Power Query

If you use this code here in Power Query you can then see it :

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) 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]),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([QuarterOfYear]) & " " & 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 Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
in
fnDateTable

Copy past it if you want