Dynamic File Loading

[krishnakumar_Ra]

I have a filter.. (values Test1 and Test2)

Folder has base files starting with Qualifier Test1*.csv or Test2*.csv
If I choose Test1, PBI should load all the files start with Test1*.csv... Like wise Test2*
In a nutshell the input files must be dynmaic based on Filer or a parameter

[v-jianpeng-msft]

Hi, @krishnakumar_Ra 

Merry Christmas to you. Previously we have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li

[v-jianpeng-msft]

Thank you danextian and Kedar_Pande 

Hi, @krishnakumar_Ra 

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

Create a table to use as slicer options:

Return to the desktop and bind our parameters to this slicer column:

Create a slicer:

Dynamic Switching:

Best Regards

Jianpeng Li

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

[danextian]

In Power Query, create a parameter called FileStart or whatever you want  to call it. In the query that is connected to the folder, filter it the file name Begins With then select parameter from ABC option and select FileStart.  You need not add the asterisk if you're confident that no other files contain anything else after Test1, etc otherwise modify the M code of that step to something like

= Table.SelectRows(#"Name of Previous Step", each Text.StartsWith([Name], FileStart & "*"))

If you want to case-proof the filter, convert both your parameter and [Name] to upper or lower case in the Table.Select step

= Table.SelectRows(#"Name of Previous Step", each Text.StartsWith(Text.Upper([Name]), Text.Upper(FileStart) & "*"))

This wil ensure that the names will get filtered regardless of the text case use in FileStart or the names themselves.

[krishnakumar_Ra]

First Thanks for quick Response

Can we pass selected Value here...

= Csv.Document(File.Contents("C:\Users\Myfolder\TestCase#1.csv"),[Delimiter=",", Columns=33, Encoding=1252, QuoteStyle=QuoteStyle.None])
Where Testcase#1 comes from Filter Dropdown
_____________________________________________
You response

= Table.SelectRows(#"Name of Previous Step", each Text.StartsWith([Name], FileStart & "*"))

Where I need to look for this.. Sorry for basic questions

[Kedar_Pande]

@krishnakumar_Ra 

In power query,  create a Parameter for the Filter.

Go to Home > Get Data > Folder, and select the folder where the Test1*.csv and Test2*.csv files are located.
In the Power Query Editor, use the parameter to filter the Name column in the folder contents.
Select the Name column.
Go to Home > Keep Rows > Keep Rows Where > Begins with.
In the dialog, type FilePrefix & "*" to dynamically filter files that start with the value of FilePrefix.

Add Custom Column

if Text.StartsWith([Name], FilePrefix) then [Content] else null

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[krishnakumar_Ra]

I need to select all Testcase#1 files from the folder.. Can you share me where I need to make the change.. in PQ