How do I import set ranges from many worksheets in Power BI?

[fatanalyst]

Hi guys, 

I'm having a problem with my import in the query editor. I am trying to pull data from a master workbook that we keep for weekly sales:

• There are 100+ stores; each store has a worksheet within the master workbook
• about half of these stores are in the US with the other half being in Canada
• Each worksheet basically looks like the worksheet below

My problem is that:

• For the US stores it is correctly importing the 52 rows of data (first set of week 1-52)
• but the Canadian stores are importing 104 rows of data

An example of a Canadian store excel worksheet is below, as you can see the first set of weeks 1-52 is the $USD weekly sales whilst the second set of weeks 1-52 (in the lower half of the worksheet) is the $CAD weekly sales. The weekly sales are reported in Canadian dollar and then we convert it to the $US dollar amount.

I have used a blank query and this formula to get the workbook = Excel.Workbook(FileContents("name of workbook.xlsm", null, true)

I only need the first set of weeks 1-52 from each worksheet but as stated above, for the canadian stores it's importing both sets of weeks 1-52. 

Any help would be appreciated! And thanks for the great community Power BI!

 

[v-xjiin-msft · Accepted solution]

Hi @fatanalyst,

=> I only need the first set of weeks 1-52 from each worksheet but as stated above, for the canadian stores it's importing both sets of weeks 1-52. 

In your scenario, you want to import the top 52 rows of the worksheet into Power BI. Right?

As I know, currently it is unable to separate the data in one same sheet directly and load the first part of the data into Power BI. But to achieve your requirement, you can try following method:

1. The easiest way, after you importing the entire worksheet. In Home pane there're options Keep Rows or Romove rows.

2. Add a flag row in your worksheet, then use Power Query function Table.SelectRows() to check this row and only import the rows before this flag row.

Thanks,
Xi Jin.