cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ToddChitt Member
Member

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

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.

 

2018-08-07_17-27-44.png

What is going on?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
chaz2jerry Regular Visitor
Regular Visitor

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

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

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

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 Regular Visitor
Regular Visitor

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

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

 

Community Support Team
Community Support Team

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

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Support Team
Community Support Team

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

The report ID is CRI 79697545.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chaz2jerry Regular Visitor
Regular Visitor

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

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