Inconsistent Data in Excel

[Daviejoe]

Hi,

I have an issue with an Excel data source.

Every so often, the excel data source will be saved down and it will have two additional rows above my headers.  The top row will have a merged cell that spans across the length of the data extract/all columns and it will contain text "Max export limit reached".

The second row will be a blank row.

On other occasions the export will not contain the additional two rows and will display my data in a perfect tabular format and I have no issues.

How do i create a query that will effectively have an IF statement in it?

Thanks in advance.

David

[MattAllington · Accepted solution]

In power query

connect to the table with the error issue

delete the "changed type" step and lpromote header" steps if they are there. Column names should be column 1, column 2 etc

put a filter on column 1 to exclude the blank and the error message you mentioned.

then promote the first row, etc. 

when you connect to a file without the 2 extra rows, the filters will simply do nothing. 

[Daviejoe]

Hi @MattAllington ,

big thanks for the reply, much appreciated.

Loved Supercharge Power BI btw!

Regards

David