Table data not extracted - Power query

[Ahmed Hashish]

Hello,
I am getting data from an excel workbook within a folder
Sheet has 3-4 rows at the top with couple of titles and blank rows,
Then there is a table below these few rows.
I am cleaning these few rows by removing them and then promoting headers from the table below.
The issue is that some data(columns) are not extracted into the power query editor,
And some columns (not all) in the table show null data.
I tried to remove some title (merged cells) above the table before running the query, and this works perfectly
and the data previously shown as null are now visible.

How do I overcome this within power query editor, without manually interfering with the source file?
As there are other users that are going to use this file and I am trying to make it user-friendly and automated as possible.

Thanks!

 

[RoryA]

I take it the data is not an actual Table, just a rectangular range? Can you make it into a real Table?

 

[Ahmed Hashish]

I take it the data is not an actual Table, just a rectangular range? Can you make it into a real Table?

I suppose so, it's probably not an actual table.
Is there a way to identify it as a table using power query editor?
I would like to not edit the source file if possible.

Thanks

 

[RoryA]

No, a table is an excel feature.

Any chance you can post your current query and a sample file (e.g. on OneDrive - with made-up data)?

 

[GraH]

Is that excel file being update manually? Or is it generated by another system?

If the file is being updated manually, then there is no opposition to do a little tweak. Besides doing a conversion from the range to an actual table in excel, once can create a dynamic name reference too.
Power Query should pick that up nicely.

There is a way actually to see in PQ whether the "table" is a table. Here I connected to a file.

The sheet kind contains all the data on the sheet.
The table kind contains only the table data. This is a table object in Excel.
The last one is a defined name, a range on sheet1.

To make the latter dynamic, there are plenty of examples on the net to be found.

 

[Ahmed Hashish]

Solved
Converting the source data from .xls (excel 97-2003) to .xlsx (excel workbook) shows all data and solves the issue.
For this to work, the file has to be "saved as" and changing the extension from the dialog box and NOT changing extension type from the file name.