Forum Discussion
Error when trying to load to data model
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:
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...
4 Replies
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
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...
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...
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.
