Error when trying to load to data model

[cathoms]

Help! I built a query and it is doing what I want (see last image) but it won't load to the data model. I get the following error message:

Here is the query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\csk4117\Documents\Working files\Covid IP Admit-Discharge.xlsx"), null, true),
    #"Covid IP Admit-Discharge Dates_Sheet" = Source{[Item="Covid IP Admit-Discharge Dates",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Covid IP Admit-Discharge Dates_Sheet",{{"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"HAR", type text}, {"MRN", type text}, {"EPIC LOC ID", Int64.Type}, {"Admit DTS", type datetime}, {"Discharge DTS", type datetime}, {"Length Of Stay Day QTY", Int64.Type}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type1", "AdmitDate", each DateTime.Date([Admit DTS]), type date),
    #"Inserted Date1" = Table.AddColumn(#"Inserted Date", "DischargeDate", each DateTime.Date([Discharge DTS]), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Date1",{"Admit DTS", "Discharge DTS"}),
    Custom1 = Table.AddColumn(#"Removed Columns", "CensusDate", each {Number.From([AdmitDate])..Number.From([DischargeDate])}),
    #"Expanded CensusDate" = Table.ExpandListColumn(Custom1, "CensusDate"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded CensusDate",{{"CensusDate", type date}})
in
    #"Changed Type2"

Which produces this:

[cathoms]

That allows me to load the data, but then I lose the information I need. CensusDate just becomes two rows for each HAR - the AdmitDate and DischargeDate. I want a row for each date from Admit to Discharge so that I can get a census/distinct count of HARs by date.

Unless there is some way from each set of rows to count HARs for the dates inbetween...

[cathoms]

Also, I checked and I don't have any Null values in the AdmitDate or DischargeDate columns. There are a few in the MRN but even removing that column doesn't help.

[amitchandak]

@cathoms , please check this seems wrong

Custom1 = Table.AddColumn(#"Removed Columns", "CensusDate", each {Number.From([AdmitDate])..Number.From([DischargeDate])}),

try like

Custom1 = Table.AddColumn(#"Removed Columns", "CensusDate", each {Number.From([AdmitDate]), Number.From([DischargeDate])}),

, try to correct the formula 

[cathoms · Accepted solution]

I hate when this happens but after long hours of frustration and trying to figure out the issue it turns out I had some blank rows buried in the source file. A simple remove blank rows fixed the problem...