cancel
Showing results for 
Search instead for 
Did you mean: 

This query does not have any columns with the supported data types.......

Since having the September 2020 PowerBI Desktop update I've started running into an issue with a number of my report templates (pbit files) that use a function to retrieve CSV data from a selected folder.   The error message I receive "This query does not have any columns with the supported data types. It will be disabled from being loaded to the model"  and it appears during the initial data load after I have entered the parameter value on opening the template.

 

These are the steps that I take to use the template report:

 

 - Open the PBIT file

 - On opening, there is a pop-up box (parameter) that asks you to enter a location on your computer.

 - A funtion in power query then uses that location to get the CSV files located there

 - This data is then loaded into the report

 

Since the Sept update though it no longer works.   See below screenshot:

 

RyanBentham_0-1602164065614.png

 

Any ideas?

 

 

Status: New
Comments
Community Support

@RyanBentham 

 

I cannot reproduce the issue. You may try the latest version to recreate the template file.

New Member
I ran into this when my lists didn't have any values
Advocate V

@v-chuncz-msft      Makes no difference

 

@paisleygray      That's not that case for me. 

 

 

For anyone with the same issue, a possible work-around at the moment is to rename the function and then create a new template.     This works for me, not sure why, but i'll take it and hopefully whatever this issue is gets sorted in a future release. 

 

 

Advocate I

@RyanBentham @v-chuncz-msft I have absolutely the same issue.

 

Ryan, thank you for sharing the idea to rename functions but I wonder if the issue appears again after renaming.

Advocate V

@KsenAnt 

 

Looks like the issue can re-appear even after renaming the function(s) unfortunately.    However, this only seems to happen when I make a change to the underlying report and then update the template.  In this case you can again just rename the function(s) and create a new template.  Not ideal, and i'm not sure if i'm just getting 'lucky' but it seems to be resolving the issue.

 

What I have found though is if you create the template and it works as expected then it shouldn't just stop working or fail on the refresh for no reason - it should be good to last.   This issue only seems to resurface (at times) when you make a change to the underlying report and then update the template. 

 

Also,  I'm not sure if this issue happens with all functions.  For me it only happens with a function that is used to get the CSV contents from a set of files in a folder.   

 

eg:

let
Source = (x) => let
Source = Csv.Document(x,[Delimiter=",", QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"
in
Source

 

 

 

 

Advocate I

@RyanBentham in my case, the issue appeared only for the custom functions, so I don't have anything to say about other cases.

Did you see that a new PBI desktop update has been provided (19 Oct 2020)? Looks like it contains a fix for this issue as well.

Regular Visitor

Hi folks,

i've the same issue, not only with power query function, but also with parameters.

 

Parameters are managed like pq , so in the model they're loaded like a table.

 

I've found a work around for functions: 

1) highlight the function
2) press right button

3) go to properties
4) view these & uncheck properties flags, if they're enabled

5) press enter

6) Apply & Close

7) Clean PowerBI cache from Option Menù

😎 Export the PBIT file

 

By the above steps, at next launch of the template, it works.

 

Like Ryan says, it's not ideal, and i'm not sure if i'm just getting 'lucky' but it seems to be resolving the issue.

I hope that PowerBI Team corrects asap this terrible issue.

 

Simone

Advocate I

@simoneschiavon thank you for sharing your experience but unfortunately, I didn't get what properties of custom functions you changed. I don't have anything in the properties menu. 

@RyanBenthamcontinuing our conversation I can confirm that the trick with renamed functions works just until you try to create a new template basing on the report generated from the previous report. The new version of the template will be broken as soon as you try to load data again. So, you have to rename functions and/or parameters every time before generating a new template. That's crazy....

Regular Visitor

Hi KsenAnt,

sorry for my worst English..

I mean these flags under properties in the specific function/parameter/query in Power Query:
Immagine.png

Disable these flags, if they're enabled:

immagine_2020-10-22_124717.png

Atfer that, follow my latest instructions.

It's a workaround, for this moment, perhaps...

I'm waiting for fixing this bug by Power Bi Team (hope 🤔 )

Simone

Advocate I

@simoneschiavon @RyanBentham try to disable the option "Auto-detect field types"AutoDetect.png