PowerBI only getting first row from excel, lists null for most other rows

[utnapishtem]

The table in Excel has data:

However, in PowerBI, for some reason, it decided that I want null for everything except the first row:

I checked in Excel to be sure the data type is constant for each column, but still PowerBI wants these rows to be null.  Notice that sa couple values came through, but things like Date certainly didn't.  What should I start investigating?  This kind of thing seems to happen every quarter, and I end up just importing again to create a duplicate table in PowerBI and it works fine.  I have so many duplicates now, it's a pain.

[tsibilski]

I've got the same issue with files pulled from Oracle (EAS). The short term solution is opening those files in Excel and just saving them. Long one, TBC.

[v-lili6-msft]

hi  @utnapishtem 

You may try use ctrl+T to create a table then load into power bi

https://community.powerbi.com/t5/Desktop/Get-Data-from-Excel-GhostSheet1-also-defined-Table-vs-raw-d...

Regards,

Lin

[amitchandak]

@utnapishtem , Under Data Transformation, there is an option to delete blank rows. Delete those rows and check you might have other rows.

There is an option Delete rows under that delete blank/empty rows

[AllisonKennedy]

can you copy and paste the code from the Power Query Advanced Editor screen? What changes from quarter to quarter?

[utnapishtem]

Thanks for the pointer to the advanced editor!  It helped me find my mistake that caused the issue - I saw the data source in there was pointing to a OneDrive location that didn't exist on this machine.

However, after that was fixed, none of the data would load because for some reason, PowerBI decided to create relationships between same named columns in completely different tables.  I had to cool down for a while before I came back to avoid damage to my PC.  I deleted every relationship that was automatically created and things are working again.  Every quarter, some new problem caused by PowerBI, and a few caused by me, I guess.

[AllisonKennedy]

Glad you sorted it. Refresh Preview is a handy option under that query editor as well.

You can turn off the Automatically detect relationships on data load under File > Options.