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
v-jiascu-msft Super Contributor
Super Contributor

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.

View solution in original post

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

 

v-jiascu-msft Super Contributor
Super Contributor

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.

View solution in original post

v-jiascu-msft Super Contributor
Super Contributor

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

sayslah Frequent Visitor
Frequent Visitor

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

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


Capture.JPG

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.

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 61 members 1,326 guests
Please welcome our newest community members: