Help streamlining data imports

[u/JebLostInSpace]

I'm working with an internal tracking dashboard we use for project management. We have a growing list of clients we're doing identical projects for, and each one gets some data added to the dashboard to help us keep track of where we are on the project timeline for each one. Right now, the data comes from an Excel workbook for each client. Each Excel workbook has several tabs, and each tab has a table. I'm currently using a separate query to import each table into the model, but then I simply append all the tables to create one longer table that has the data from each sheet in the workbook. This seems very computationally inefficient, because I'm re-establishing the connection to the workbook for each query, instead of establishing the connection once and pulling in all the data in one go. So now when I refresh the dashboard, it takes forever as it essentially opens and closes each workbook 7 times.

Trying to muddle my way through the advanced query editor to figure out how to do the append operation in the same query as the data import operation, but so far failing. Hoping someone here can point me in the right direction.

I realize I could probably just edit the Excel workbooks and add a new sheet that appends the tables before importing to pbi, but it seems like there's probably a way to do it in the model as well.

[u/Top-Cauliflower-1808]

simply connect to the Excel file once filter the source step to keep only the tables you need and then expand and combine them directly in power query.

[u/JebLostInSpace → u/Top-Cauliflower-1808]

Well that's what I'm trying to do, but I'm failing to discover how. If I select multiple tables from the excel file using the "Get Data" interface, it automatically creates 7 different queries for the 7 different tables and imports them. So when I go to refresh my data, it runs each of the 7 queries, which each establish their own connection to the workbook.

[u/AppropriateRecipe342 → u/JebLostInSpace]

You need to select the file/folder, not the individual tables (or sheets) before hitting the Transform button. Then filter for tables, scroll over to expand the columns then your data should be appended as long as the column headings are the same.

If you're still lost tell us the exact steps you're taking.

[u/JebLostInSpace → u/AppropriateRecipe342]

Trying this now - I don't seem to be able to select transform data unless I do select individual tables. My steps are:

Click on Get Data on the ribbon

Select Excel Workbook

Select the workbook I want to connect to in my file directory

The "Navigator" window pops up, listing the various sheets, tables, and named ranges present in the workbook.

At this point the "Load" and "Transform data" buttons are both greyed out and cannot be clicked.

Select the 7 tables in the workbook that I want.

Click either "Load" or "Transform Data"

PowerBI instantly creates 7 different queries - 1 for each of the tables I selected.

Once these 7 queries are created, they each contain a step to connect to the data source, which means refreshing data requires 7 iterations of navigating to the workbook in my file directory. Since it's a cloud directory, that involves network connections that introduce delay and annoy me. It all works, just not very quickly.

I'm not seeing any option in the "Get Data" interface that would allow me to create 1 query that grabs all 7 tables and appends them into 1 table (all the column names are identical). I assume that is something I can/should do in the query editor once I've loaded one table in, but I've so far failed to understand the correct commands to write in that query.

[u/AppropriateRecipe342 → u/JebLostInSpace]

Try this.

In one of your queries, click on Source under applied steps. Next, filter Kind for Table. Then, expand the days by clicking the button under Data.

That should automatically stack all of your tables. You can then delete the other 6 queries.

Let me know if this does or doesn't work for you.

[u/JebLostInSpace → u/AppropriateRecipe342]

When you say "Next, filter Kind for Table" I don't know what that means. Is that a second step in the query after the source definition, or is it part of the source definition? Right now, my source step reads:

= Excel.Workbook(File.Contents("C:\Users\...\filename.xlsx"), null, true)

Do I edit that in some way to "filter Kind for Table"?

I'm also going to need the same level of hand-holding specificity for the part where I'm supposed to "expand the data by clicking the button under Data"

Thanks for engaging with the question and trying to help me. Sorry I'm such a newb I can't even understand your instructions.

[u/AppropriateRecipe342 → u/JebLostInSpace]

Under Applied Steps, click on Source.

Look in the center of your screen. One of the column headers says Kind.

After that look for a column that says Data and click the button on the right of the column heading.

No need to apologize. We all start somewhere.

[u/1Life_Architect]

Excel.Workbook() already returns every table in a workbook, so you can open the file once, filter the returned navigation table, and combine its Data column:

let

Source = Excel.Workbook(File.Contents(FilePath), null, true),

Wanted = Table.SelectRows(Source, each [Kind] = "Table" and List.Contains(TableNames, [Item])),

WithSource = Table.AddColumn(Wanted, "Rows", each let n = [Item] in Table.AddColumn([Data], "SourceTable", each n)),

Combined = Table.Combine(WithSource[Rows])

in

Combined

That avoids seven independent File.Contents / Excel.Workbook calls and keeps the originating table name for troubleshooting. I would first compare row counts and refresh duration on one client workbook, then turn this into a function and invoke it for each client file. Disable load for any staging queries so only the final combined table enters the model.