Table.Profile Bug

[Anonymous]

 Team,

I have noticed two bugs with Table.Profile() in the latest version of Power BI (and probably earlier versions):

1. Right clicking a Table.Profile() step has "View Native Query" greyed out which means that query folding is not occuring.  However, I know that query folding is ocurring because...
2. I get the following error when executing Table.Profile against a column cast as type uniqueidentifier:

DataSource.Error: Microsoft SQL: Operand data type uniqueidentifier is invalid for min operator.
Details:
    DataSourceKind=SQL
    DataSourcePath=<MS CRM DB>
    Message=Operand data type uniqueidentifier is invalid for min operator.
    Number=8117
    Class=16

Thanks,

Simon

[v-qiuyu-msft]

Hi @Anonymous,

1. Based on my research, the step with Table.Profile() function doesn't support Query Folding. You can run SQL Profiler, then repeat the step with Table.Profile(), there is no related trace in SQL Profiler. 

2. I'm not very clear about " executing Table.Profile against a column cast as type uniqueidentifier". Would you please share detail information so we can try to reproduce it. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi Qiuyu, Vicky

There is a data type in SQL Server type called uniqueidenfier.  Running a Table.Profile() against it causes a SQL error (in 2008, at least) because you can't MIN() it in SQL.  

Also, Table.Profile() does query fold.  As evidence, I have included the top few rows (the whole script is massive):

select min([rows].[DateKey]) as [Min/DateKey],
    max([rows].[DateKey]) as [Max/DateKey],
    avg(convert(float, [rows].[DateKey])) as [Average/DateKey],
    stdev([rows].[DateKey]) as [StandardDeviation/DateKey],
    count(1) as [Count/DateKey],
    count(1) - count([rows].[DateKey]) as [NullCount/DateKey],
    count(distinct([rows].[DateKey])) as [DistinctCount/DateKey],
    min([rows].[Date]) as [Min/Date],
    max([rows].[Date]) as [Max/Date],
    (
        select dateadd("ns", (([$Item] - [$tpd] * floor([$Item] / [$tpd])) - 10000 * floor(([$Item] - [$tpd] * floor([$Item] / [$tpd])) / 10000)) * 100, dateadd("ms", floor(([$Item] - [$tpd] * floor([$Item] / [$tpd])) / 10000), dateadd("d", floor([$Item] / [$tpd]), convert(datetime2, '0001-01-01 00:00:00')))) as [$Item]
        from 
        (
            select 864000000000 as [$tpd],
                convert(bigint, avg([$groupVar1])) as [$Item]
        ) as [$Table]
    ) as [Average/Date],
    count(1) as [Count/Date],
etc...

 Furthermore, how else could Power Query return the SQL Server error, "Microsoft SQL: Operand data type uniqueidentifier is invalid for min operator", if no folding is happening?  That doesn't make any sense...

Lastly, in all honesty guys, I don't know why this post was marked as "needs more information".  I had provided everything clearly and succinctly in my original post.

Simon

[Anonymous]

If anyone experiences the same bug as me, please use the below M code to bypass it until it is fixed:

let
    Source = TableName,
    ValidColumns = Table.SelectRows(Table.Schema(Source), each [NativeTypeName] <> "uniqueidentifier")[Name],
    FilterToValidColumns = Table.SelectColumns(Source, ValidColumns),
    Profile = Table.Profile(FilterToValidColumns)
in
    Profile

[Anonymous]

No response guys?  This has been active over 1 month now.  I even PM'd Vicky to re-open it.

[v-qiuyu-msft]

Hi @Anonymous,

Based on my test in the latest Power BI desktop version 2.56.5023.861 64-bit (March 2018), Table.Profile() works fine for uniqueidentifier column. Please update your Power BI desktop version then test again. 

Best Regards,
Qiuyun Yu 

 

[Anonymous]

Hi @v-qiuyu-msft

Thanks for responding!

The bug still exists.  I am running it against the MS CRM contacts entity on the below setup:

Power BI version: 2.58.5103.501 64-bit (May, 2018)

SQL Server: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)   Aug 19 2014 12:21:34   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Evidence:

Thanks,

Simon