Inconsistency when loading data from Excel (aka first column & row are null)

[EsgibtkeinBier]

Hi everyone!

When loading two different excel reports of the same format from a sharepoint folder, sometimes (at the same step), the first column and row appear as nulls, while others they don't.

-------------------------------------------------------------------------------------------------------------------------

Example

Excel Sheet

Nulls

No nulls

-------------------------------------------------------------------------------------------------------------------------

Any idea about what causes this/how to avoid or force Power BI to load the data in a specific way so to avoid inconsistencies such as the above?

Thank you very much for your time!

[nvprasad · Accepted solution]

Hi,

As you see in your excel image, the data starts from Row Number 3 hence first 2 rows are showing as nulls in Powerquey. Below are the  two optoins to fix this.

1. Transform date in Powerquery like delete first 2 rows then make first row as header.

2. Make excel data as table (Select data and hit Ctrl +T) and select table instead of sheet from PowerBI.

Appreciate a Kudos! ‌‌
If this helps and resolves the issue, please mark it as a Solution! ‌‌

Regards,
N V Durga Prasad

[EsgibtkeinBier]

Loading the data as a table seemed to work great! Thank you very much.

However, is there any idea why this was caused in the first place?

We were lucky in the sense that the excel reports already had the data in a table format. Since they were numerous, it wouldn't be easy to manually transform into tables and because of the inconsistency in the navigation step, it wouldn't be as easy transforming them in power (unless doing it conditionally Conditionally removing a column?).