Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jujiro-eb
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
jujiro-eb
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
jujiro-eb
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."

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.

ITSNev
Frequent Visitor

This now seems to have been fixed my Microsoft.

Smauro
Solution Sage
Solution Sage

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

@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.

lbendlin
Super User
Super User

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")

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.

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

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors