Dynamic File Loading

Anonymous

Not applicable

1 year ago

Thank you danextian and Kedar_Pande

I will share two methods for dynamic data sources. If your data source is an Excel file, then slicers cannot be used with it. If your data source is a database that supports DirectQuery mode, you can use slicers to dynamically control the selected table. This is my conclusion.

In my example, there are two Excel tables and two SQL Server tables, and the value columns are 10 and 20 respectively.

Method 1:

Create a parameter that is a list:

Use this parameter for our sheet query:

let
    path1 = "C:\Users\xxxx\Desktop\New folder (2)\Book",
    pa = Text.From( bookNum),
    path2 = ".xlsx",
    res = path1 & pa &path2,
    Source = Excel.Workbook(File.Contents(res), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

Return to desktop to switch parameters:

Method 2:

Similarly, create a parameter:

Use this parameter for our database query:

let
    Source = Sql.Databases("xxx"),
    Test = Source{[Name="Test"]}[Data],
    tableNum = Text.From(TableNum),
    item = "Table" & tableNum,
    dbo_Table1 = Test{[Schema="dbo",Item=item]}[Data]
in
    dbo_Table1