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

[Syndicate_Admin]

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

[Anonymous]

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 · Accepted solution]

@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]

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

[Syndicate_Admin]

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

[BA_Pete]

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

[Syndicate_Admin]

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]

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