cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AJ_vizMan
Helper II
Helper II

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

Accepted Solutions
AJ_vizMan
Helper II
Helper II

@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

6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

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

AJ_vizMan
Helper II
Helper II

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

Syndicate_Admin
Administrator
Administrator

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

 

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

AJ_vizMan
Helper II
Helper II

@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

Syndicate_Admin
Administrator
Administrator

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Users online (2,178)