Hot to import a specific data range from an excel file

[Anonymous]

Hi,

I'm trying to import a specific data range from an excel file in to Power BI. The first few rows and columns have some complex formatting consisting of a shape with a few pictures and irrelevant data that I'd like Power BI to completely ignore and NOT bring in.

The excel file has two sheets, 'Cover Page' and 'Mailing'. I only need to bring in the specific range from the 'Mailing' sheet. As explained above, the 'Mailing' sheet has complex formatting I'd like Power BI to ignore. The data range I'd like Power BI to bring in is from A9:M8454 with A9 through M9 being the column headers.

I'm not sure how to go about accomplishing this. I tried bringing in the entire sheet. I had planned on creating some kind of flag that would allow me to filter out the irrelevant rows of data but Power BI brings in the entire sheet as blank values. I believe the complex formatting in the first few rows (that I'd like to ignore) is causing this issue.

Any suggestions/help would be greatly appreciated



Thank you,
Andrew

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

For example, when my Excel data is like this.

After connecting, click transform data.

Add an index column as the row number in Excel.

Now add a filter to the index column.

Select the column you want to remove and click the remove column button

Delete the index column and click this button.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kirtigogia]

Hi,

Load the 'Mailing Sheet' and choose the Edit option. Now go to Home tab and select 'Remove Rows'. Use 'Remove Top Rows' / 'Remove bottom rows' option in order to delete N number of Top rows/ Bottom rows respectively (which have complex formatting that you don't want to keep in). Please find below the screenshot for your reference: