Multiple Excel Sources = Many Null Rows

[Ruksuro]

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!

[v-xjiin-msft]

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:

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]

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]

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]

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]

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

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.

[Floriankx]

Hello,

are you sure they are actually blank?

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

[Ruksuro]

Hi Florian,

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