cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
utnapishtem
Helper I
Helper I

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

The table in Excel has data:

utnapishtem_0-1593736561287.png

 

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

utnapishtem_1-1593737111122.png

 

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.

6 REPLIES 6
tsibilski
Regular Visitor

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
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
AllisonKennedy
Super User
Super User

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

Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.

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.

Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors