Query runs in Desktop but not Power BI Service!?

[Cbischel]

Hi Everyone,

I have a query that runs without issue in Power BI Desktop but when loaded to the Power BI service it fails to refresh and sends out the following warning:

Message:	We cannot convert the value "[Value]" to type Record. Table: kpiELTRatios.
Cluster URI:	WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net

The problem query is a reference to another query that pulls in records from salesforce. This original query does not have any issues refreshing but the reference query tied to it seems to fail to refresh in the Power BI service. When I investigate in Desktop the query works without issue. Ive tried to rebuild it several different ways without any type conversions and I still get the issue. See the code of the query in question below.

let
    Source = factInterventionSessions,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Program_Name__c] = "Enrichment")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Intervention_Session_Date__c", "School__c"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "School-Date-ID", each [School__c] & "-" & Text.From([Intervention_Session_Date__c])),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"School__c", "Intervention_Session_Date__c", "School-Date-ID"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"School-Date-ID"}, {{"Count", each Table.RowCount(_), type number}, {"AllColumns", each _, type table}}),
    #"Expanded AllColumns" = Table.ExpandTableColumn(#"Grouped Rows", "AllColumns", {"School__c", "Intervention_Session_Date__c"}, {"School__c", "Intervention_Session_Date__c"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded AllColumns", {"School-Date-ID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"School-Date-ID"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"School__c", type text}, {"Intervention_Session_Date__c", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"School__c", Order.Ascending}, {"Intervention_Session_Date__c", Order.Ascending}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows",{"School__c", "Intervention_Session_Date__c", "Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"School__c", "School"}, {"Intervention_Session_Date__c", "Date of ELT"}, {"Count", "Students Attending ELT"}})
in
    #"Renamed Columns"

Any support you can provide is appriciated!

[v-huizhn-msft]

Hi @Cbischel,

The same query statement runs correctly in Power BI desktop but not in Service, it's strange. Have you done any other operation before you refresh it in Power BI service?

In addition, for the reason of the error message, @MarcelBeug has provided a detailed solution in the following similar threads. Hope they are useful.

Expression Error - We cannot convert a value of type Record to type Table
Expression.Error: We cannot convert a value of type Table to type List.  

Best Regards,
Angelia