Direct Query Report fails Sept 2020 Desktop

[edhans]

I created a new report using the September 2020 Desktop version using Direct Query. It runs fine on the desktop and all M code is folded and I can see the native query.

There are two other small tables that I just keyed in (they relate to how fields will work with the DAX SWITCH() function) but are not used by any of the queries in the M code.

The report is connected to the gateway fine and I can trigger a refresh which works. It is connected to an on-prem SQL Server. Again, 100% functional in the PBI Desktop, and nothing works in the service.

But when I publish to the service, I get this for every visual using the direct query table.

The "technical details" are:

Activity ID3c90c1a5-bd9e-4e8a-b863-2ec6c1cf60ae
Request ID0ede5131-493e-d992-3e6c-83fa8f5b5370
Correlation IDad18f583-2e29-de8b-f94e-7a21076e6e98
TimeWed Sep 23 2020 10:50:50 GMT-0700 (Pacific Daylight Time)
Service version13.0.14296.74
Client version2009.1.02987-train
Cluster URIhttps://wabi-west-us-redirect.analysis.windows.net/

Is anyone else seeing this before I start troubleshooting and open a ticket?

[v-yuta-msft]

@edhans ,

Could you please share some part of the M code or share a sample pbix file using onedrive for business so that I could do further analysis?

Regards,

Jimmy Tao

[edhans]

Hi @v-yuta-msft ,

I think I found the issue, and not sure if it is a bug or not. I was using List.Contains() within a Table.SelectRows. It was per the logic in this blog post. This folds just fine, and in Direct Query mode on the desktop app, it works just fine, but when published to the service, it fails.

The M code is:

let
    Source = Sql.Database("ServerName", "DataBase"),
    dbo_APVENFIL_SQL = Source{[Schema="dbo",Item="APVENFIL_SQL"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(dbo_APVENFIL_SQL,{{"vend_no", "Vendor Number"}, {"vend_name", "Vendor Name"}, {"country", "Country"}, {"contact", "Contact"}, {"phone_no_2", "Cell Number"}, {"phone_no", "Factory Phone Number"}}),
    #"Filtered Rows for type code" = Table.SelectRows(#"Renamed Columns", each ([vend_type_cd] = "FFWRD" or [vend_type_cd] = "IMP  ")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows for type code",{"Vendor Number", "Vendor Name", "Country", "Factory Phone Number", "Cell Number", "Contact"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{{"Vendor Number", Text.Trim, type text}, {"Vendor Name", Text.Trim, type text}, {"Country", Text.Trim, type text}, {"Factory Phone Number", Text.Trim, type text}, {"Cell Number", Text.Trim, type text}, {"Contact", Text.Trim, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each List.Contains(UsedVendorNumbers, [Vendor Number]))
in
    #"Filtered Rows"

The SQL Native query generated is this:

select [_].[t0_0] as [Vendor Number],
    [_].[t1_0] as [Vendor Name],
    [_].[t2_0] as [Country],
    [_].[t3_0] as [Factory Phone Number],
    [_].[t4_0] as [Cell Number],
    [_].[t5_0] as [Contact]
from 
(
    select ltrim(rtrim([_].[vend_no])) as [t0_0],
        ltrim(rtrim([_].[vend_name])) as [t1_0],
        ltrim(rtrim([_].[country])) as [t2_0],
        ltrim(rtrim([_].[phone_no])) as [t3_0],
        ltrim(rtrim([_].[phone_no_2])) as [t4_0],
        ltrim(rtrim([_].[contact])) as [t5_0]
    from 
    (
        select [_].[vend_no],
            [_].[vend_name],
            [_].[country],
            [_].[phone_no],
            [_].[phone_no_2],
            [_].[contact]
        from [dbo].[APVENFIL_SQL] as [_]
        where [_].[vend_type_cd] = 'FFWRD' and [_].[vend_type_cd] is not null or [_].[vend_type_cd] = 'IMP  ' and [_].[vend_type_cd] is not null
    ) as [_]
) as [_]
where [_].[t0_0] in ('AME900', 'CSL100', 'GUA101', 'GUA103', 'JTF100', 'NIN125', 'NIN700', 'RON101', 'XIA100', 'YUY101')

It only fails in the service.

[ab14]

I'm facing the same issue. I've no transformations in my table which is connecting to a on-prem SQL server in Direct Query mode. When I delete the table from the model and re-introduce it, it works fine. Not sure if there's a bug in the latest update or something else.

[edhans]

Interesting.... I may try adding back my List.Contains() and see if that works now... I just whacked that line and it works fine.

[Goofr]

Hi,

I have the same problem.

After I've updated PBI Desktop to september 2020 release, all my direct query reports freeze after opening.

I query Azure sql db and Oracle db.

When I recreate the tables all is good.

Any help would be appreciated!

Kind regards,

Frank