Append OneDrive worksheet tables dynamically

[gk2go]

I have a worksheet with multiple tables, with a new one added each day.
Importing the worksheet In Power BI Desktop conveniently appends all the tables in the worksheet into one table named after the query name.

However if I try to import the worksheet with power bi service (to avail of the OneDrive hourly refresh) it creates a table for each table in the workbook instead of appending then into one. How should I deal with this?

[v-joesh-msft]

Hi @gk2go ,

After my research, I'm afraid it couldn't achieve in Power BI for now. For your requirement,  you can come up with a new idea and add your comments there to make this feature coming sooner.

https://ideas.powerbi.com/forums/265200-power-bi-ideas

In addition, I found an idea similar to your needs, you can vote:

Enable OneDrive Refresh for Excel files loaded from OneDrive Personal in Power BI Desktop via URL

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gk2go]

Voted!

It's the same in dataflows, but there you can edit the query to consolidate all the Tables into one with this code:

Source = Excel.Workbook(Web.Contents("https://sharepoint.com/sites/GK/Shared Documents/Workbooks/data.xlsx"), null, true),

#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Table")),

#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"Data"})

[v-joesh-msft]

Hi @gk2go ，

Yes, it’s just that there will be a refresh limit of 8 times a day. Maybe you need Power BI Premium licensing, if the dataset resides on a Premium capacity, you can schedule up to 48 refreshes per day in the dataset settings.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gk2go]

Indeed, or since the data is in OneDrive Microsoft makes it work for free with the OneDrive Refresh, per the idea you posted.