cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ruksuro Member
Member

Multiple Excel Sources = Many Null Rows

Hi All,

 

A user of ours is trying to extract data from multiple (10) excel sources (all same format, in the same folder).

 

He is bringing these in as individual queries and then combining them all. Unknowingly he was trying to bring in >10,000,000 blank rows to one meta-table.

 

I've tried two fixes to this to no avail:

  • Add a 'remove blank rows' or filter out where [key] doesn't exist for each of the subqueries (as early as possible).
  • Create a single query that starts with the subfolder and combines them.

 

All three of these scenarios appears to preview fine but then loads for a very long time (file size doesn't continue to increase so I think it's loading the blanks).

 

Any thought? What have I missed?

 

Thanks all!

1 ACCEPTED SOLUTION

Accepted Solutions
Ruksuro Member
Member

Re: Multiple Excel Sources = Many Null Rows

Hi Xjiin,

 

Thanks for your suggestion, while it didn't resolve it (they were definately null rows, not blank, "" or " "), it did prompt me to try removing unneeded columns before rows.

 

This seemed to sort it, I don't know the nuances but perhaps it is more efficient to filter row-wise on a narrower table?

 

Thanks all for your interest and help.

 

Ruksuro.

View solution in original post

8 REPLIES 8
Floriankx Established Member
Established Member

Re: Multiple Excel Sources = Many Null Rows

Hello,

 

are you sure they are actually blank?

Maybe there are some space ' '. You could try Text.Trim.

Ruksuro Member
Member

Re: Multiple Excel Sources = Many Null Rows

Hi Florian,

 

Yep, they are blank, appear as null in query editor.

 

v-xjiin-msft Super Contributor
Super Contributor

Re: Multiple Excel Sources = Many Null Rows

Hi @Ruksuro,

 

=> He is bringing these in as individual queries and then combining them all.

 

How did he do this combining? Use Merge Queries option in Edit Queries? If so, what's the join type? If he was using Left or Right Join. The combining might generate null rows. Please refer to following sample:

 

1.PNG2.PNG3.PNG4.PNG

 

By the way, if you can share us some of the source files with One Drive or Google Drive. It will help us a lot to repro the issue and find out where the issue is.

 

Thanks,
Xi Jin.

Ruksuro Member
Member

Re: Multiple Excel Sources = Many Null Rows

Hi Xi Jin,

 

He is using table.combine({table1},...,{table2}), I don't think this is a join rather than concatenation of tables (I've confirmed all columns have the same name.

 

The nulls appear in each of the individual queries, not subsequent from the join.

 

We've tried adding the "remove blank rows" step as early as possible each of the constituant tables but this doesn't work.

 

Oddly, the preview looks fine, but when clicking "close and apply" it loads to a consitent point (~55MB) and then just continues to load without increasing. Left it for a further half hour but it didn't load in that time.

 

There is really very little data in each file (~100 rows) - not the best way of storing data by far but we're trying to help him get used to the tool in a familiar setting before introducing SQL.

 

Thanks!

msornakumar Regular Visitor
Regular Visitor

Re: Multiple Excel Sources = Many Null Rows

Hi ,

 

 If all the columns are having null , that should be removed by "Remove Blank Rows"

 

  For further testing can you please go to the Advanced editor and the blank as well in the remove blank rows and add the " " as well as highlighted in BLUE below and let us know the results.

 

    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null," "})))

Ruksuro Member
Member

Re: Multiple Excel Sources = Many Null Rows

Hi msornakumar,

 

Thanks for the suggestion, I will try this out monday (currently unable to test).

 

Will get back to you ASAP.

 

 

v-xjiin-msft Super Contributor
Super Contributor

Re: Multiple Excel Sources = Many Null Rows

Hi @Ruksuro,

 

Did you resolve your issue? If not, please share us your pbix file if possible. If you can't, some screenshots will also help for us to troubleshoot your issue.

 

Thanks,
Xi Jin.

Ruksuro Member
Member

Re: Multiple Excel Sources = Many Null Rows

Hi Xjiin,

 

Thanks for your suggestion, while it didn't resolve it (they were definately null rows, not blank, "" or " "), it did prompt me to try removing unneeded columns before rows.

 

This seemed to sort it, I don't know the nuances but perhaps it is more efficient to filter row-wise on a narrower table?

 

Thanks all for your interest and help.

 

Ruksuro.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,901)