Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Syndicate_Admin
Administrator
Administrator

Blank rows are there after combining 2 data files in Power BI data Table

Hi, there are 2 files  e.g. File 1_Resolved tickets and File 2_Unresolved tickets. Both contain similar data. Files are combined using Power query editor and it combined correctly. But, when i see combined data table then there are 2 issues :

1. Column headers are repeated and coming as new row

2. There are multiple blank cells in the data table.

M Code is attached as available in advanced editor.

 

Could you please help me to figure out the reason of above 2 issues and how to overcome it?

 

#Power BI #Power Query

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

@Syndicate_Admin  - Thank you for your response.

In share Point hosted Excel files, i deleted the blank rows and that's it. No blanks in Power Query imported data.

Than you so much for quick easy fix.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi @Syndicate_Admin ,

 

Glad to know your issue has been solved by yourself😀 Could you please kindly Accept your last reply as the solution to make the thread closed. More people will benefit from it.

 

Best Regards,
Eyelyn Qin

Syndicate_Admin
Administrator
Administrator

@Syndicate_Admin  - Thank you for your response.

In share Point hosted Excel files, i deleted the blank rows and that's it. No blanks in Power Query imported data.

Than you so much for quick easy fix.

mahoneypat
Microsoft Employee
Microsoft Employee

Sounds like you are not promoting headers in your Transform Sample File query.  Is that the case?  You can do that there, or just filter out the repeated header row in your final query.  You can also filter out blank rows in either.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Syndicate_Admin
Administrator
Administrator

@Syndicate_Admin - Thanks for your response. Mi code is attached now.

Hi @Syndicate_Admin ,

 

Thanks for providing the screenshot.

Unfortunately, this only seems to show your main query code, not your example file transformation code.

However, I'm inclined to agree with @mahoneypat - it does indeed sound like you've not promoted headers in your example file transformation.

Regarding the blank rows, I suspect that it is a similar problem i.e. blank rows haven't been removed within your example file transformations. Excel sources very often include blank rows in Power Query imports due to the way the bounds of the data are defined, and due to lack of data validation which you would find in SQL databases etc.

Within your PQ query list, you should see a table query (not 'fx' query) called 'Transform Sample File from ...' or similar. This is the query in which you need to make these transformations in order for them to be applied to both of your sample files.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Your method worked. I applied the following at "Transform Sample File" after the last applied steps: = Table.SelectRows(#"Promoted Headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

BA_Pete
Super User
Super User

Hi @Syndicate_Admin , @AJ_VizMan,

 

I can't see any M code attached to your post.

Did you forget it or did I misunderstand?

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors