cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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.

5 REPLIES 5
Highlighted
Super User IV
Super User IV

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

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

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Super User IX
Super User IX

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

@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 !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Regular Visitor

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

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.

Highlighted
Super User IV
Super User IV

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

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.

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Community Support
Community Support

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

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors