DirectQuery errors on imported tables. July 2018 Composite Models.

[freder1ck]

So, I turned Composite Models preview feature on yesterday to review it. I got the top two errors on my imported tables.

I turned it off and restarted PBID and refreshed all. Then, I got the third error on a different table. I also continue to get the error on the other table.

[AlexGorev]

Thank for reporting this issue. Engineering team is investigating. 

[edhans]

@AlexGorev I sent in a PBIX file via support. SR number 118072418646926 if that helps.

[AlexGorev]

We are working on the fix and we'll update the Desktop as soon as we have it. Sharing the workaround and request for additional information:

Issue:

In some cases where import vs DQ mode is not explicitly specified when creating the query, the mode is converted from import to DQ when refreshing with the composite models feature switch on, causing a conflict.

Workaround:

1. For the broken query, get the query text by right clicking in the query editor and selecting advanced editor.
2. Create a new query that is explicitly import, such as a CSV file.  Enter any information you want in the get data wizard, so long as it creates a new query.
3. Right click the new query, select advanced editor, and paste the query text saved in step 1

Additional ask:

While we have some PBIX files that are in this state, we would like to better understand how people are triggering this problem.  We believe it is related to creating a query using the “Blank Query” functionality.  Is this how you are creating the queries that end up in a broken state?  Are you creating them with the composite model feature switch on or off?

[edhans]

@AlexGorev,

Every one of my PBIX files has a query in it that started as a Blank Query - my date table that I generate using M code, but the query in the file I submitted that is getting this bogus conversion to Direct Query is the result of the Append Queries as New menu item, which is Table.Combine().

I know for a fact I did not create that query from a blank query and manually do the Table.Combine statement.

I've resent the file that got corrupted by this per the PBI Support team, so hopefully you have a copy now to test with.

EDIT: Oh, and I created it months ago before this new feature. All I have to do to get the corruption is open the file with this feature set to On and refresh the file. It gives the warning about the new feature, then tells me I have transformations in a query that aren't supported by DirectQuery. So the new function is making the change for me, I didn't edit a thing.

[tonymaclaren]

 I have tried the suggestions by Alex.

File 1. All my queries are shown as import if I hover over them.The queries load fine, but the date table will not work as a calendar table even though it has a 1 to Many relationship with the fact table and marks sucessfully as a date table. Bizarely, the same date table (Blank M Query) above works fine in an older model that was loaded into the Desktop with the consolidated models preview feature disabled before loading.!!

I am now going to test if File 1 displays the same behaviour in the June release and will post the results.

I have a feeling that the major rework of the relationships part of Power BI to allow for many to many may be the culprit.

[tonymaclaren]

I have attached my Date Query and I would be grateful if someone would kindly test it and see if they can make it work as a calendar table. you will need to add a parameter  StartOfFiscalYear if you want to see the fiscal columns.I did not remove the parameter because I dont want to modify the code in any way.

let
    StartDate = #date(2017, 1, 1),
    Custom1 = StartDate,
    EndDate =  Date.EndOfMonth(DateTime.Date(DateTime.LocalNow())),
    #"Extracted Date" = Date.From(EndDate),
    NumberOfDays = Duration.Days( #"Extracted Date" - Custom1 ),
    Dates = List.Dates(Custom1, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([FullDateAlternateKey]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Day Name", "Fiscal Year", each if [Month]>=StartOfFiscalYear
then [Year]+1
else [Year]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Fiscal Year", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Fiscal Period", each if [Month]>=StartOfFiscalYear
then [Month]-(StartOfFiscalYear-1)
else [Month]+(12-StartOfFiscalYear+1)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Fiscal Period", Int64.Type}}),
    #"Inserted Division" = Table.AddColumn(#"Changed Type2", "Inserted Division", each [Fiscal Period] / 3, type number),
    #"Rounded Up" = Table.TransformColumns(#"Inserted Division",{{"Inserted Division", Number.RoundUp, Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Rounded Up",{{"Inserted Division", "Fiscal Quarter"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Day of Week", Int64.Type}, {"Day", Int64.Type}, {"Week of Month", Int64.Type}, {"Week of Year", Int64.Type}, {"Quarter", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type3", "Merged", each Text.Combine({"FQ-", Text.From([Fiscal Quarter], "en-GB")}), type text),
    #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "Merged.1", each Text.Combine({"FY-", Text.From([Fiscal Year], "en-GB")}), type text),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Merged Column1", "First Characters", each Text.Start([Month Name], 3), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted First Characters",{{"First Characters", "MMM"}, {"Merged", "FQ-N"}, {"Merged.1", "FY-YYYY"}}),
    #"Inserted Merged Column2" = Table.AddColumn(#"Renamed Columns2", "Merged", each Text.PadStart(Text.From([Month]),2,"0")),
    #"Duplicated Column" = Table.DuplicateColumn(#"Inserted Merged Column2", "Fiscal Year", "Fiscal Year - Copy"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Duplicated Column",{{"Fiscal Year - Copy", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type4", "Custom", each [#"Fiscal Year - Copy"]&[Merged]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Fiscal Year - Copy"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns",{{"Custom", "MMYYYY"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns3",{"Merged"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns1",{{"MMYYYY", "Period ID"}}),
    #"Inserted Merged Column3" = Table.AddColumn(#"Renamed Columns4", "Merged", each Text.Combine({"Q", Text.From([Quarter], "en-GB")}), type text),
    #"Renamed Columns5" = Table.RenameColumns(#"Inserted Merged Column3",{{"Merged", "QN"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns5", "Index", 1, 1),
    #"Changed Type5" = Table.TransformColumnTypes(#"Added Index",{{"Index", Int64.Type}}),
    #"Renamed Columns6" = Table.RenameColumns(#"Changed Type5",{{"Index", "Calendar ID"}}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns6",{{"Period ID", Int64.Type}}),
    #"Renamed Columns7" = Table.RenameColumns(#"Changed Type6",{{"FullDateAlternateKey", "Date"}}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns7",{{"Date", type date}})
in
    #"Changed Type7"

[freder1ck]

Alex,

"Are you creating them with the composite model feature switch on or off?"

• I had created the model before composite model feature was released.
• The query in question may very well have been created from a blank query.

Thanks,

Fred

[tonymaclaren]

1. Create a new blank query
2. open the advanced editor
3. delete the contents you see
4. paste the contents from my code in
5. change the start date to the earliest year in your model close and load.
6. turn off auto date time in options (new feature ) because otherwise, PBI will continue to create many hidden calendar tables automatically. in relationships connect the calendar table to whatever date field you want to use.
7. you will need to add one new parameter which is easy.
8. in the query editor manage parameters dropdown. New parameter-- name it exactly   StartOfFiscalYear

Type=Decimal value

Suggested Values =Any

Current Value= 7

Finally, once you have loaded the table, you need to mark it as date table. This is a recent new feature.

You may have to remove the default summarisation on some columns. There should be none on the calendar table.

Sort numeric columns for the correct display in visuals.

With this approach you can add a calendar table in a minute to your new project !

[AlexGorev]

We released an update to Power BI Desktop through Windows Store and Download Center today that will prevent this issue from hapenning in the future.

Thanks,

Alex.

[edhans]

Thanks @AlexGorev - I can confirm the file I was testing this with that had a query changed to Direct Import works fine after opening it in this version.

I had restored the file from a backup, so I don't know if this new version fixes the stuff it broke.