Table data not extracted - Power query

Ahmed Hashish

New Member
Joined
Oct 15, 2023
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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!🌹
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I take it the data is not an actual Table, just a rectangular range? Can you make it into a real Table?
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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.
1707990360177.png

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.
 
Upvote 0
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.
 
Upvote 0
Solution

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top