Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
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:
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |