this query contains transformations that can't be used for directquery

[ToddChitt]

I'm getting the error shown below for a Date table that is generated from a custom M Function. 

How is a GENERATED table classified as either DirectQuery OR Import? It is NEITHER, in my opinion, and especially with the new feature of "Comples Models" set to ON, this GENERATED table should work with ANY other tables.

What is going on?

[Shivam_Kathpal]

Hi All, 

I am also facing the same error when I am trying to get data from Amazon redshift in direct query mode, althogh till yesterday, data was coming correctly, but today it is showing an error that "This query contains transformations that can't be used for DirectQuery."

[chaz2jerry]

I have same issue since the July update (Composite modeling preview), following is my M query, not sure what I can do to fix this yet.  

let CreateDateTable = (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])),
InsertFisYearNumber = Table.AddColumn(InsertMonth, "FisYearNumber",each if [MonthOfYear] >= 4 then [Year] else [Year] -1 ),
InsertFisYearDisplay = Table.AddColumn(InsertFisYearNumber, "FisYear" ,each "FY" & Text.End(Number.ToText([FisYearNumber],"D",""),2)),
InsertFisMonth = Table.AddColumn(InsertFisYearDisplay, "FisMonth", each if [MonthOfYear] >= 4 then [MonthOfYear] - 3 else [MonthOfYear] + 9 ),
InsertFisQuarterNumber = Table.AddColumn(InsertFisMonth, "FisQuarterNumber", each if [QuarterOfYear] > 1 then [QuarterOfYear] -1 else [QuarterOfYear] + 3),
InsertFisQuarterDisplay = Table.AddColumn(InsertFisQuarterNumber ,"FisQuarter", each "FQ" & Number.ToText([FisQuarterNumber],"D","") ),
InsertDay = Table.AddColumn(InsertFisQuarterDisplay , "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]) & " " & 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],1)+1),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),
InsertCurrentThursday = Table.AddColumn(InsertWeekEnding, "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), type date),
InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),
InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOWeekYear", each Date.Year([CurrentThursday])) ,
InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each
if [CurrentThursday] < [ISOWeekJan4]
then Date.AddDays([CurrentThursday],-3)
else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )
,type date),
InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),
InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOWeekYear] * 100 + [ISOWeekNum], type number),
InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOWeekYear]) & "W" & Text.End( "0" & Text.From(([ISOWeekNum]*10) + [DayInWeek]),3)),
InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOWeekYear]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Text.From([DayInWeek])),

fnPeriod445a = (weekNum) => let
Periods =
{
{(x)=>x<5, [P=1,Q=1,M="Jan"]},
{(x)=>x<9, [P=2,Q=1,M="Feb"]},
{(x)=>x<14, [P=3,Q=1,M="Mar"]},
{(x)=>x<18, [P=4,Q=2,M="Apr"]},
{(x)=>x<22, [P=5,Q=2,M="May"]},
{(x)=>x<27, [P=6,Q=2,M="Jun"]},
{(x)=>x<31, [P=7,Q=3,M="Jul"]},
{(x)=>x<35, [P=8,Q=3,M="Aug"]},
{(x)=>x<40, [P=9,Q=3,M="Sep"]},
{(x)=>x<44, [P=10,Q=4,M="Oct"]},
{(x)=>x<48, [P=11,Q=4,M="Nov"]},
{(x)=>true, [P=12,Q=4,M="Dec"]}
},
Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}
in
Result,

InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeekNum])),
ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q" }, {"Period445", "Quarter445"}),
RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon"})
in
RemovedColumns
in
CreateDateTable

[ToddChitt]

I started a new, clean model, added my 'Generate Dates' custom M function, invoked it to get a table, and when I Close and Load, I get the error!

IT'S THE ONLY QUERY IN THE MODEL!!! AND it doesn't connect to anthing OUTSIDE of the model.

So I turn off the preview feature of Composite Models, and STILL get the same error. 

BUG?

[chaz2jerry]

I found a thread for this issue, I am trying the fix from MS now.

https://community.powerbi.com/t5/Issues/DirectQuery-errors-on-imported-tables-July-2018-Composite-Mo...

[v-jiascu-msft · Accepted solution]

Hi All,

I have reported this issue to the Product Team. I will update here later. The old issue of @chaz2jerry's link has been fixed. The workaround is applying other queries first then adding the function query.

Best Regards,

Dale

[v-jiascu-msft]

The report ID is CRI 79697545.

Best Regards,

Dale

[chaz2jerry]

Yes I followed the solution from the other link and it worked for me:

- Turn off Composite Model Preview setting.

- Close PBI and restart.

- Create new blank query or blank table.

- Transfer the M query codes to the new blank query.

- Remove the old query that is causing the issue (M query for date table).

[Anonymous]

I got this error but from a different route. I will explain it here since maybe others got here the same way I did:

1) I created a new .pbix file and selected DirectQuery for Data Connectivity Mode

2) I duplicated one of the queries I connected to in Power Query Editor (doesn't matter which one and if you only have one query, just duplicate that one) and renamed it 'Database' (because it is my data source connection for easy database changing in many queries. Ignore this if you do not understand, I am simply stating it for the steps that I generated this error)

3) In the advanced Editor window of the query I duplicated, I changed the query name after the in statment to say source to create my datasource query. 

---

@chaz2jerry wrote:

Yes I followed the solution from the other link and it worked for me:

- Turn off Composite Model Preview setting.

- Close PBI and restart.

- Create new blank query or blank table.

- Transfer the M query codes to the new blank query.

- Remove the old query that is causing the issue (M query for date table).

---

4) Then I used my updated 'Database' query to be my source in my other queries in the let statement so that the first line reads:

     Source = Database

5) When I hit Close And Apply, Voila, I get this error. 

The solution that allowed me to maintain DirectQuery while keeping my 'Database' query as my source for the other queries was to simply disable loading by right clicking my 'Database' query in Power Query Editor and clicking the checkmark next to 'Enable load' which greys out the query so that you can close and Apply.

[solenttest1]

i I have come across the same issue error message and have no idea what it means. So that I can getround the issue can someone explain clearly what the eror is and what I need to address in my query6?

I have convert(date,DATEADD(month, DATEDIFF(month,0,[DateOfContact]), 0)) as MonthOfContact, would that be an issue, I have left joins and cross apply i query too.

Helpand a solution would be appreciated?

Thanks