How to split date rang record into multiple rows in power query

Hi AhmedBadr_81 all columns with dates must be of date type (not datetime). You simply replaced my type transformation step with a ref to your data. Try this 

let
    s = Excel.CurrentWorkbook(){[Name="NewTest"]}[Content],
    source = Table.TransformColumnTypes(s,{{"Day-off Start Date", type date}, {"Day-off End Date", type date}}),
    f = (s as date, e as date) => 
        List.Generate(
            () => [som = s, eom = List.Min({Date.EndOfMonth(s), e}), d = Duration.Days(eom - s) + 1],
            (x) => x[som] <= e,
            (x) => [som = Date.AddMonths(Date.StartOfMonth(x[som]), 1), eom = List.Min({Date.EndOfMonth(som), e}), d = Duration.Days(eom - som) + 1]
        ),
    a = Table.AddColumn(source, "rec", (x) => f(x[#"Day-off Start Date"], x[#"Day-off End Date"])),
    expand_list = Table.ExpandListColumn(a, "rec"),
    expand_rec = Table.ExpandRecordColumn(expand_list, "rec", {"som", "eom", "d"}, {"start", "end", "days"}),
    z = Table.RemoveColumns(expand_rec,{"Day-off Start Date", "Day-off End Date", "# Days"})
in
    z

Hi AlienSx 

First of all, thank you so much for your valuable feedback, I already copied your written query and did minor modifications to the source to synchronize with my dataset path. but I found an error in step a (as shown in the pic below):

and this is your query after my modifications:

let
    s = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszITU1RcEpMKVLSUTLUNzbQNzIwMgayjfQNYUxjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Day-off Start Date" = _t, #"Day-off End Date" = _t, #"# Days" = _t]),
    source = Excel.CurrentWorkbook(){[Name="NewTest"]}[Content],
    f = (s as date, e as date) => 
        List.Generate(
            () => [som = s, eom = List.Min({Date.EndOfMonth(s), e}), d = Duration.Days(eom - s) + 1],
            (x) => x[som] <= e,
            (x) => [som = Date.AddMonths(Date.StartOfMonth(x[som]), 1), eom = List.Min({Date.EndOfMonth(som), e}), d = Duration.Days(eom - som) + 1]
        ),
    a = Table.AddColumn(source, "rec", (x) => f(x[#"Day-off Start Date"], x[#"Day-off End Date"])),
    expand_list = Table.ExpandListColumn(a, "rec"),
    expand_rec = Table.ExpandRecordColumn(expand_list, "rec", {"som", "eom", "d"}, {"start", "end", "days"}),
    z = Table.RemoveColumns(expand_rec,{"Day-off Start Date", "Day-off End Date", "# Days"})
in
    z

I tried to change the type to date but still have an error.