Data source error: Expression.Error: We cannot convert the value null to type Table.

[Jinaldaiya09]

Hello, Need Help
I am Getting an Issue at Power BI Service Schedule Refresh. But Runs Good at Power BI desktop.
My datasource is = OneDrive.
For Some of the Files I am Getting "." In Filename Or and Sometimes I get "#", When Combining Files in power BI based on First File.

I have written Code to Manage This In Power BI Desktop:
Transform File:

let
Source = (Parameter18 as binary) =>
let
// Try to load the Excel workbook
          Workbook = try Excel.Workbook(Parameter18, null, true) otherwise null,
// Try to access the first sheet's data if workbook loaded successfully
          FirstSheet = if Workbook <> null and Table.RowCount(Workbook) > 0 then
               try Workbook{0}[Data] otherwise null
          else
              null
          in
             FirstSheet
in
      Source

Transform Sample File

let
Source = Excel.Workbook(Parameter18, null, true),
#"Planned vs# Tracked Hours " = Source{0}[Data]
in
#"Planned vs# Tracked Hours "

Error Message at Service End

Data source error: Expression.Error: We cannot convert the value null to type Table.. Microsoft.Data.Mashup.ErrorCode = 10277. . The exception was raised by the IDbCommand interface.

[v-sshirivolu]

Hi @Jinaldaiya09 ,

Try exploring a few things here, as one of them might help get around the issue you're seeing. The error you're running into, where Power Query says it cannot convert a null value to a table, usually happens when a file being processed is missing the expected structure. This could be something like a sheet not being present, or the workbook not containing any valid data to extract. Even if most files work fine, a single file with an unexpected format or structure can cause the whole step to break. To help with this, you can update the logic inside your Transform Sample File query so that it checks whether content actually exists before trying to extract it.
Try this M code you can try using there. It makes the source is not null or empty before moving forward, which helps prevent this type of error when a file is missing a sheet or has no data.

let
Source = Excel.Workbook(Parameter1, null, true),
Sheets = if Source = null or Table.IsEmpty(Source) then #table({"Name", "Data", "Item", "Kind", "Hidden"}, {}) else Source,
FilteredSheets = Table.SelectRows(Sheets, each ([Kind] = "Sheet")),
FirstSheet = if Table.IsEmpty(FilteredSheets) then null else FilteredSheets{0}[Data]
in
FirstSheet

[v-sshirivolu]

Hi @Jinaldaiya09 ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[grazitti_sapna · Accepted solution]

Hi @Jinaldaiya09,

Try below M code

let
Source = (Parameter18 as binary) =>
let
Workbook = try Excel.Workbook(Parameter18, null, true) otherwise null,
FirstSheet = if Workbook <> null and Table.RowCount(Workbook) > 0 then
try Workbook{0}[Data] otherwise null
else
null,
// Ensure output is always a table
SafeTable = if FirstSheet = null then
#table({}, {}) // empty table with 0 rows and 0 columns
else
FirstSheet
in
SafeTable
in
Source

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Jinaldaiya09]

Hello @grazitti_sapna 

Firstly Thank You!
The Solution you suggested, does not worked, I have paste the Suggected solution to Transfom File.

I have not remaned any column as well in Power Query.

can you please help what mistake I am making

[grazitti_sapna]

Hi @Jinaldaiya09,

Error shows that there is a missing column in your file, this could be due to reasons like

• The sheet is empty.

• The column names are different.

• The sheet layout varies across files.

Your Transform File function assumes the first sheet always exists and has the same structure, but your SafeTable logic only guarantees a table; it doesn’t guarantee the expected columns exist.

Try below DAX

let
Source = (Parameter18 as binary) =>
let
Workbook = try Excel.Workbook(Parameter18, null, true) otherwise null,
FirstSheet = if Workbook <> null and Table.RowCount(Workbook) > 0 then
try Workbook{0}[Data] otherwise null
else
null,
SafeTable = if FirstSheet = null then
#table({"Column1","Column2","Column3"}, {}) // replace with expected columns
else
// Ensure the table has the expected columns
Table.SelectColumns(
FirstSheet,
{"Column1","Column2","Column3"},
MissingField.UseNull
)
in
SafeTable
in
Source

Table.SelectColumns(..., MissingField.UseNull) ensures that all expected columns exist, filling missing columns with null instead of throwing an error.

You can identify the column which is added with null values and try to resolve the issue.