Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
cathoms
Helper V
Helper V

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:

cathoms_0-1618414394667.png

 

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_1-1618414853129.png

 

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

View solution in original post

4 REPLIES 4
cathoms
Helper V
Helper V

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.

 

cathoms_0-1618416303289.png

 

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.

amitchandak
Super User
Super User

@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 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.