Forum Discussion
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.
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.
"AwarenessPower 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."
9 Replies
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.
"AwarenessPower 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-backup-files
Report backup filesUpdating 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:
- 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
- Find a copy of the report there with the name and time stamp of the original file.
- Copy the file to a location you would prefer, in order to preserve it.
- 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.
This now seems to have been fixed my Microsoft.
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.
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 ]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.
