cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

M expression(s) not working in SQL queries during refreshes from Portal (Help)

In my Power BI report, I was computing dates using M-expressions.  I used these dates in the sql queries without any issue for more than six months since the report's inception.  All of a sudden, around June 26, 2020, any usage of these M expressions in sql queries result into credential error whenever I try to refresh the data on the portal.  The same behavior works when the refresh is done from PBI Desktop.

I get the following error:
Processing error: Credentials are required to connect to the Odbc source. (Source at dsn=mydsn.)

Strangely, if I use the parameters instead of M Expression(s), everything works as expected.  I have created an isolated demo to reproduce the behavior.

 

p_date (parameter) = 06/29/2020

p_days (parameter) = 5

var_calculated_date = 
let
    Source = Date.FromText("2020-06-26"),
    add_days = Number.FromText(p_days),
    new_date = Date.AddDays(Source, add_days)
in
    Date.ToText(new_date, "MM/dd/yyyy")

Good Query = 
let
    Source = Odbc.Query("dsn=mydsn", "select 'The supplied parameter is " & p_date & "' as Data")
in
    Source

Bad Query = 
let
    Source = Odbc.Query("dsn=mydsn", "select 'The supplied var is " & var_calculated_date & "' as Data")
in
    Source

 

Bad Query, when included in refresh and enabled to load would give credential error on PBI portal.  I have tried building the dynamic sql string in a separate statement, to no avail.

PBIX file can be obtained from this link.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Apparently, some recent software update at the PBI portal broke something.  
Symptoms: If enhanced dataset mode is enabled in your PBIX, M expressions stop working.

Microsoft has recognized this to be an issue and will provide a fix on 7/21/2020.


"Awareness

Power BI Customers using the Enhanced dataset metadata preview feature may see refresh not complete with the following message "Credentials are required to connect to the File source". As a workaround, users can disable the "enhanced dataset metadata (Preview)" feature in Power BI Desktop. Engineers are working on a fix and expect it to be deployed by end-of-day 07/21/2020."

View solution in original post

9 REPLIES 9
Highlighted
Super User II
Super User II

p_days is not a text, it is a number.

 

let
    p_days = 5,
    Source = Date.FromText("2020-06-26"),
    new_date = Date.AddDays(Source, p_days)
in
    Date.ToText(new_date, "MM/dd/yyyy")
Highlighted

The parameter p_days is not the problem as such.  

add_days = Number.FromText(p_days),

 

You can see that it was converted to number before use.  Even the expression you have provided results into SQL credential error.  As I mentioned in my original post, this issue occurs only when refresh is done on the portal.  Local PBI Desktop works as expected.

Highlighted

Does the server have different locale settings? Try specifying the dates as YYYY-MM-DD.

Highlighted
Solution Specialist
Solution Specialist

If you haven't changed any credentials between Service and Desktop, it could be that they've added some extra restrictions.

Could you try faking it as a param and see if it works?

Date.ToText(
    Date.AddDays(
        Date.FromText("2020-06-26"),
        Number.From(p_days)
    ),
    "MM/dd/yyyy"
)
meta
[
    IsParameterQuery = true,
    Type = "Text",
    IsParameterQueryRequired = false
]

 




Feel free to connect with me:
LinkedIn

Highlighted

@Smauro 

 

You had my hopes up so much!  😀

 

Unfortunately, it did not work.  Same, credential error.  Once again, PBI Desktop evaluates the parameter (which is now an expression,) correctly.  But thanks though, I did not think of that.  I had tried other things like passing parameters through a text file to the same fate.

Highlighted

@lbendlin 

 

The server hosting the portal is controlled by Azure.  I have no idea what locale settings it is set.  Assuming that everything on US East 2, it is safe to assume that it may be be using MM/dd/yyyy format.  Besides, the text to date conversion is being supplied with a format.

Highlighted
Helper I
Helper I

Apparently, some recent software update at the PBI portal broke something.  
Symptoms: If enhanced dataset mode is enabled in your PBIX, M expressions stop working.

Microsoft has recognized this to be an issue and will provide a fix on 7/21/2020.


"Awareness

Power BI Customers using the Enhanced dataset metadata preview feature may see refresh not complete with the following message "Credentials are required to connect to the File source". As a workaround, users can disable the "enhanced dataset metadata (Preview)" feature in Power BI Desktop. Engineers are working on a fix and expect it to be deployed by end-of-day 07/21/2020."

View solution in original post

Highlighted

If you cannot wait until 21st, then you can recover from an auto created backup.
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enhanced-dataset-metadata#report-back...

Report backup files

Updating a report to use the enhanced dataset metadata feature is irreversible. During the update, however, a report backup file is create to save a version of the report in its original (pre-update) format. The backup file is removed after 30 days.

To locate the backup report file, do the following:

  1. Navigate to the following location: C:\Users\<user>\AppData\Local\Microsoft\Power BI Desktop\TempSaves\Backup. If using the Microsoft Store version of Power BI Desktop, use the following location: C:\Users\<user>\Microsoft\Power BI Desktop Store App\TempSaves\Backups
  2. Find a copy of the report there with the name and time stamp of the original file.
  3. Copy the file to a location you would prefer, in order to preserve it.
  4. Make sure the Enhanced metadata format preview feature is disabled in Power BI Desktop if you choose to open or use that original file.

The backup file is created when the report is upgraded, so any changes made after the upgrade are not included. New reports created when the Enhanced metadata format feature is enabled do not have a backup file.

Highlighted
Frequent Visitor

This now seems to have been fixed my Microsoft.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors