Forum Discussion

EsgibtkeinBier's avatar
EsgibtkeinBier
Frequent Visitor
4 years ago

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

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....
Need Help
nvprasad's avatar
nvprasad
Solution Sage
4 years ago

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's avatar
EsgibtkeinBier
Frequent Visitor
4 years ago

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