How to dynamically create power queries in a loop for each existing table in a folder?

[jaryszek]

Hello Guys,

I have code which is retriving all tables in a folder:

Now i can filter first table and have it as sepetarated query. 
But what i want to do is to create all queries automatically for me in a loop.

Something like :
1. Loop through all tables within folder
2. Create seperate query for each table 

How to automate it?

Best,
Jacek

[Chewdata]

Hey!

I am affraid that it is not possible to create seperate new queries for each file from one central query. 

[jaryszek]

Thanks! 

what about programatically approach like for example via tmdl files?

Best,
Jacek

[v-nmadadi-msft · Accepted solution]

Hi @jaryszek,
Thanks for reaching out to the Microsoft fabric community forum.

TMDL (Tabular Model Definition Language) is used to define and manage Power BI datasets and models programmatically. They provide a structured way to represent tables, relationships, measures, and other metadata in a model.
So TMDL alone cannot create multiple queries from a single query; it is primarily used for defining and managing dataset structures rather than dynamically generating multiple queries.

If we want to create multiple queries from a single query, we need to integrate Python or PowerShell scripting to automate the generation of .tmdl files, which can then be imported into Power BI, followed by refreshing the dataset to apply the changes.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[bhanu_gautam]

@jaryszek , Try using

m
let
// Step 1: Retrieve all files in the folder
Source = Folder.Files("C:\YourFolderPath"),

// Step 2: Filter only the files you need (e.g., CSV files)
FilteredFiles = Table.SelectRows(Source, each Text.EndsWith([Extension], ".csv")),

// Step 3: Loop through each file and create a query
CreateQueries = List.Transform(FilteredFiles[Content], each
let
// Get the file name without extension
FileName = Text.BeforeDelimiter(Text.AfterDelimiter([Name], "\"), ".csv"),

// Load the content of the file
FileContent = Csv.Document(_, [Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),

// Create a named query
NamedQuery = Table.RenameColumns(FileContent, {{"Column1", FileName}})
in
NamedQuery
)
in
CreateQueries

[jaryszek]

Thanks, but this will create list of queries within one query.

What i want to do is to automate process and create new queries each for file. 
Not creating one query but multiple automatically.


Best,
Jacek