Power Query custom column works in Desktop but returns blanks in BI Service

[Sue777]

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!

[v-tejrama]

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]

• Make sure they're both explicitly set to Date (not DateTime or Text)

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 it

Best Regards,
Tejaswi.
Community Support

[Sue777]

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

[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!

[Sue777]

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. 

[speedramps · Accepted solution]

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 

dax calandars.pbix

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)

[Sue777]

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]

RETURN

IF(

    DATEVALUE(StartDate) = DATEVALUE(EndDate),

    1,  // same‐day → count as 1

    COUNTROWS(

        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 Service

Learn more about timezones here
https://learn.microsoft.com/en-us/powerquery-m/datetime-localnow

Please click the thumbs up if this info helps !