Forum Discussion
Power Query custom column works in Desktop but returns blanks in BI Service
Hi ,
I have an issue for one of my Power Query steps between Power BI Desktop and the Power BI Service . In Desktop I’ve added a custom column called ResolvedWorkingDays (the code for custom column can be found in the screenshot), and then changed its data type to Int64. Everything works perfectly locally and after I click Close & Apply, the new column is populated with integer values as expected in BI desktop.
However, after publishing the same report to the Service (using the identical data source and triggering a full refresh), ResolvedWorkingDays comes back blank. The rest of the columns in that table load correctly.
I’ve verified:
I explicitly set its type to Int64 in the query
The dataset in Service is refreshing without errors, and that's the same data source as BI desktop
No row-level security, filters, or measures are interfering
Has anyone seen a Power Query step work fine in Desktop but silently drop to null in Service? Are there known custom M functions that behave differently in the Service’s cloud refresh engine? Any pointers on how to diagnose or work around this would be hugely appreciated—thank you!
It is best practice to put the "working days" in the Calendar dimension table, rather than the fact table
Try use this calendar table.
You may need to change the daterange parameters for your own needs
Working days = // type: 1, week begins on Sunday (1) and ends on Saturday (7). numbered 1 through 7. // type: 2, week begins on Monday (1) and ends on Sunday (7). // type: 3, week begins on Monday (0) and ends on Sunday (6).numbered 0 through 6. // // Therefore this considers Monday to Fridat as working days IF(WEEKDAY('Calendar'[Date],2) <= 5, 1, 0)
7 Replies
speedrampsSuper User
It is best practice to put the "working days" in the Calendar dimension table, rather than the fact table
Try use this calendar table.
You may need to change the daterange parameters for your own needs
Working days = // type: 1, week begins on Sunday (1) and ends on Saturday (7). numbered 1 through 7. // type: 2, week begins on Monday (1) and ends on Sunday (7). // type: 3, week begins on Monday (0) and ends on Sunday (6).numbered 0 through 6. // // Therefore this considers Monday to Fridat as working days IF(WEEKDAY('Calendar'[Date],2) <= 5, 1, 0)Hey thank you very much for your reply!
I have a DimDate table, so I use it to filter in the calculated column, and it works.
The calculated column:ResolvedWorkingDays_op3 =VAR StartDate = Issues_tab1[CREATED]VAR EndDate = Issues_tab1[RESOLUTION_DATE]RETURNIF(DATEVALUE(StartDate) = DATEVALUE(EndDate),1, // same‐day → count as 1COUNTROWS(FILTER('DimDate','DimDate'[Date] >= StartDate&& 'DimDate'[Date] <= EndDate&& 'DimDate'[IsWeekend] = 0 -- have this column to indicate whether it's weekend)))
But I'm still a bit confused why my previous custom column didn't work in the power query- speedramps
Thank you Sue777
I am glad that my Calendar (DimDate) working days table trick gave you a workarround solution.
I always use the Calendar for date intellegence.Get into the habiit of building and testing date intelligence functions once in your Calendar and using then always.
Rather than creating lots of difference versions of date logic in fact tables and dax.You have not provdied enough infomation to discover the root cause of your problem,
but it could be that the Power BI server timezone is different to your Power BI desktop timezone.
This theory is simple to test ....
Write a report that display DateTime.LocalNow
Refresh the report on Power BI desktop and make a note of the date/time
Then publish the report.
Refresh the report in the Power BI Service and compare the date/time.However, the problem may be with your source data.
Especially if you are using different version (test and live servers) in Power Bi Desktop and Power BI ServiceLearn more about timezones here
https://learn.microsoft.com/en-us/powerquery-m/datetime-localnowPlease click the thumbs up if this info helps !
- BeaBF
Sue777 Hi! Power BI Desktop uses your local machine’s locale and timezone settings, while Power BI Service uses the cloud region’s default settings (usually UTC).
Try with:
if [RESOLUTION_DATE] = null then null
else
List.Count(
List.Select(
List.Dates(
Date.From(DateTimeZone.RemoveZone([CREATED])),
Duration.Days(Date.From(DateTimeZone.RemoveZone([RESOLUTION_DATE])) - Date.From(DateTimeZone.RemoveZone([CREATED]))) + 1,
#duration(1,0,0,0)
),
each Date.DayOfWeek(_, Day.Monday) < 5
)
)BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
Thank you for your reply! I've tried with your solution but still return all blank. It really confuses me that only the workingdays column went wrong, I also have columns like workingHours and workingMonth, they're perfectly fine.
e.g. my workingMonth column is:
if [RESOLUTION_DATE] <> null
then Duration.Days([RESOLUTION_DATE] - [CREATED]) / 30
else null
and I sort of solve it using a DimDate table, you can find it in the comment I replied in another reply.
Hi Sue777 ,
Thanks for reaching out to the Microsoft fabric community forum.Try this steps :
Force Explicit Type Conversion for Dates
Instead of relying on implicit conversion, explicitly convert your dates and handle nulls up front:
= if [RESOLUTION_DATE] = null or [CREATED] = null then null
else
let
startDate = try Date.From([CREATED]) otherwise null,
endDate = try Date.From([RESOLUTION_DATE]) otherwise null,
dayCount = if startDate = null or endDate = null then null else
List.Count(
List.Select(
List.Dates(startDate, Duration.Days(endDate - startDate) + 1, #duration(1,0,0,0)),
each Date.DayOfWeek(_, Day.Monday) < 5
)
)
in
dayCount
Check Data Types in Power Query (Before Publish)In Power BI Desktop:
-
Open Power Query Editor
-
Check the data types for
[CREATED] and[RESOLUTION_DATE] -
Set Locale in Power Query:To avoid regional misinterpretation:
-
Go to the Source Step (or the date conversion step)
-
Right-click the column > Change Type > Using Locale
-
Go to Power BI Service > Dataset > Settings
Refresh in Power BI Service & Monitor Errors:-
Go to Power BI Service > Dataset > Settings
-
Trigger a manual refresh
-
Check for refresh history logs to see if any errors/warnings are shown
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find itBest Regards,
Tejaswi.
Community SupportThank you for your reply! I've checked the data type, and locale setting in power query, as well as the refreshing and data source. The live data on BI service and test data on BI desktop are the same actually, from the same SQL Server data source. It seems that the custom column M code in power query didn't work at all. I managed to solve it with a DimDate table. You can also find it in another comment of this post.
-
