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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-eqin-msft
Community Support
Community Support

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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors