Different days usinf custom column

Hi Banan 

If you want to add the custom column in power query in direct query mode,  the custom column cannot be applied, becuse in direct mode, the steps in power query have limitations,you can refer to the following link.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#data-transformation-limitations.

The direct mode have limitations in transform data and data modeling, you can refer to the following link.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#modeling-limitations

The best way is to change the direct mode to import mode, then you can use the custom column, you can refer to the following sample.(the connect mode is import)

Sample data 

Add a custom column

 

List.Sum(List.Transform(List.Dates([dt1],Duration.Days([dt2]-[dt1])+1,#duration(1,0,0,0)),each if Date.DayOfWeek(_,Day.Sunday)<5 then 1 else 0))

 

Output

If you cannot change the mode to import mode, please consider to create a measure, you can refer to the following measure.

 

Measure =
VAR a =
    ADDCOLUMNS (
        CALENDAR ( MIN ( SQLTest[dt1] ), MAX ( SQLTest[dt2] ) ),
        "Weekday", WEEKDAY ( [Date], 2)
    )
RETURN
    COUNTROWS ( FILTER ( a, [Weekday] <> 5 && [Weekday] <> 6 ) )

 

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.