Forum Discussion

Post Prodigy

1 month ago

Solved

ETL Options with files loop (powerquery or free?)

Good day,

I need help please, i have no tools and only powerbi currently with my F2/F4 Fabric subscription only apporved for august and evlopment targeted for end of November, but now stakeholder wants an mvp to show exec memebers to get the appetite going using an existing report with a full manual operation. The product has an export per client of which i will ingest once i start with fabric and loop, but the stakeholder wants to monthly export per clinet and dump the csv into a folder directlory which is timestamped. The report should pickup theres a new source file and loop through the existing files as the source file for the powerbi.

the option i have is to complie the infor all together for one big file per month and combie or take latest timesamp for 25 lients and paste it and dont say im doing it manually for the next 5 months or can powerquery do this or anyother free source tool untill im up and runningm, the stakeholder is aware this is interim. Or even using powerquery or powershell to merge loop all the the files based on it source being "Mature_client***.csv" to say combine all the mature files to append as one and then in puwerbi powerwuery i transform and clean it, i cant use fabric trial as this is only a month or can it be done with python and i run the notebook manually monthly from my pc or is the a free etl i could use to do this etc

Sorry i know its a crappy ques. I am just thinking out loud because i have no idea and dont want to lose the audience now that theres an apetite until i have fabric up and running

Regards

data engineering

Parchitect

1 month ago

Hi icassiem,

You can do the looping in Power Query regardless of timestamps.

The query below will:

Connect to a SharePoint site
Find all CSV files
Keep only files where the file name contains one of the keywords
Combine all matching files into one table
Add the source file name and source folder for audit purposes

Example

let
    SiteUrl = "https://yourtenant.sharepoint.com/sites/yoursite",

    Keywords = {"mature", "efficiency", "interna"},

    Source =
        SharePoint.Files(
            SiteUrl,
            [ApiVersion = 15]
        ),

    FilterCsv =
        Table.SelectRows(
            Source,
            each Text.Lower([Extension]) = ".csv"
        ),

    FilterKeywords =
        Table.SelectRows(
            FilterCsv,
            each
                List.AnyTrue(
                    List.Transform(
                        Keywords,
                        (k) => Text.Contains(Text.Lower([Name]), Text.Lower(k))
                    )
                )
        ),

    RemoveHiddenFiles =
        Table.SelectRows(
            FilterKeywords,
            each [Attributes]?[Hidden]? <> true
        ),

    TransformCsv =
        (FileContent as binary, FileName as text, FolderPath as text) as table =>
            let
                Csv =
                    Csv.Document(
                        FileContent,
                        [
                            Delimiter = ",",
                            Encoding = 65001,
                            QuoteStyle = QuoteStyle.Csv
                        ]
                    ),

                PromotedHeaders =
                    Table.PromoteHeaders(
                        Csv,
                        [PromoteAllScalars = true]
                    ),

                AddSourceFile =
                    Table.AddColumn(
                        PromotedHeaders,
                        "SourceFile",
                        each FileName,
                        type text
                    ),

                AddSourceFolder =
                    Table.AddColumn(
                        AddSourceFile,
                        "SourceFolder",
                        each FolderPath,
                        type text
                    )
            in
                AddSourceFolder,

    AddData =
        Table.AddColumn(
            RemoveHiddenFiles,
            "Data",
            each TransformCsv([Content], [Name], [Folder Path])
        ),

    Combined =
        if Table.RowCount(AddData) = 0
        then #table({}, {})
        else Table.Combine(AddData[Data])
in
    Combined

The important part is this:

Keywords = {"mature", "efficiency", "interna"}

This means the query will include files where the file name contains any of those words.

Examples that would be picked up:

202607_clientA_mature_export.csv
clientB_efficiency_202607.csv
timestamp_clientC_interna.csv

If you want separate outputs, for example one table for Mature and another for Efficiency, then create one query per keyword. But if all files should be appended into one table, the query above works.

Important: all files combined in the same query should have the same structure/columns.

And yes, you can definitly do this with powershell or Python, if you are want to do it that way, just ask we will figure out something.
And sorry if i misunderstand the problem above with PowerQuery looping.

19 Replies

vilasdgawali
New Member
1 month ago
This is New for me.
Parchitect
Solution Sage
1 month ago
Hi icassiem,

For the interim MVP, I would split this into phases so you can deliver something quickly and show Power BI value while working towards long term goal Fabric value.

Option 1: Fastest MVP with Power BI Desktop
CSV files in folder
→ Power BI Desktop Get Data → Folder
→ Combine & Transform
→ clean in Power Query
→ model/report in the same PBIX

This is the quickest no-extra-tool option if the CSV files have the same structure.

Option 2: Cleaner interim MVP with Dataflow Gen1(still part of Power BI package)
CSV files in SharePoint/OneDrive/folder
→ Dataflow Gen1 combines/cleans data
→ Semantic model connects to dataflow
→ Thin report connects to semantic model

This separates ETL, model, and report better than doing everything inside one PBIX.

Option 3(do it if you can in paralell or after MVP shortly): Fabric target design
Pipeline / Notebook
→ raw files into Lakehouse
→ Python/PySpark clean and standardize
→ Silver/Gold tables
→ Semantic model
→ Power BI report

This is the proper long-term design once Fabric is available.

To sell the Fabric value, I would show both:
- MVP now: Power BI / Dataflow workaround
- Future state: Fabric pipeline/notebook/lakehouse architecture

That helps stakeholders see the difference:
- less manual work
- repeatable file ingestion
- raw file retention
- better auditability
- easier client/file loops
- better governance
- clearer monitoring and capacity visibility

So short recommendation:
Power Query Folder Combine = fastest MVP
Dataflow Gen1 = cleaner temporary architecture
Fabric Pipeline/Notebook/Lakehouse = long-term scalable solution

I would present the current method as Phase 1 MVP, not the final architecture.

🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist
💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster.
- icassiem
  Post Prodigy
  1 month ago
  Parchitect Thank You
  I cant go option1 as the files were be dumped/copied manually into the sharepoint directory, so theres 5 subfolders = 5 powerbi sources but within the folders its datestamped_clientname_xyz"mature"xyz_xyz and some has it differently clientname_xyz"efficiency..._.." etc before i can select the combine and transform and in case i could say client* or remove the timestamp and tell the user manual copy to keep it to client_mature for an example but then again theres new clients that i cant just pickup in powerbi
  
  option 2, i dont know if "Dataflow Gen1" can do this wildcard loop and write to sharpeoint again for the MVP and is part of my current powerbi pro license for me to use? (how, links, example please if this can)
  
  option 3, i was thinking python or powershell or something where i could run it manually from my pc to prep the 5 folder directories into 5 sources files to prep the raw files then powerbi does the rest?

Parchitect

Solution Sage

1 month ago

Hi icassiem,

You can do the looping in Power Query regardless of timestamps.

The query below will:

Connect to a SharePoint site
Find all CSV files
Keep only files where the file name contains one of the keywords
Combine all matching files into one table
Add the source file name and source folder for audit purposes

Example

let
    SiteUrl = "https://yourtenant.sharepoint.com/sites/yoursite",

    Keywords = {"mature", "efficiency", "interna"},

    Source =
        SharePoint.Files(
            SiteUrl,
            [ApiVersion = 15]
        ),

    FilterCsv =
        Table.SelectRows(
            Source,
            each Text.Lower([Extension]) = ".csv"
        ),

    FilterKeywords =
        Table.SelectRows(
            FilterCsv,
            each
                List.AnyTrue(
                    List.Transform(
                        Keywords,
                        (k) => Text.Contains(Text.Lower([Name]), Text.Lower(k))
                    )
                )
        ),

    RemoveHiddenFiles =
        Table.SelectRows(
            FilterKeywords,
            each [Attributes]?[Hidden]? <> true
        ),

    TransformCsv =
        (FileContent as binary, FileName as text, FolderPath as text) as table =>
            let
                Csv =
                    Csv.Document(
                        FileContent,
                        [
                            Delimiter = ",",
                            Encoding = 65001,
                            QuoteStyle = QuoteStyle.Csv
                        ]
                    ),

                PromotedHeaders =
                    Table.PromoteHeaders(
                        Csv,
                        [PromoteAllScalars = true]
                    ),

                AddSourceFile =
                    Table.AddColumn(
                        PromotedHeaders,
                        "SourceFile",
                        each FileName,
                        type text
                    ),

                AddSourceFolder =
                    Table.AddColumn(
                        AddSourceFile,
                        "SourceFolder",
                        each FolderPath,
                        type text
                    )
            in
                AddSourceFolder,

    AddData =
        Table.AddColumn(
            RemoveHiddenFiles,
            "Data",
            each TransformCsv([Content], [Name], [Folder Path])
        ),

    Combined =
        if Table.RowCount(AddData) = 0
        then #table({}, {})
        else Table.Combine(AddData[Data])
in
    Combined

The important part is this:

Keywords = {"mature", "efficiency", "interna"}

This means the query will include files where the file name contains any of those words.

Examples that would be picked up:

202607_clientA_mature_export.csv
clientB_efficiency_202607.csv
timestamp_clientC_interna.csv

icassiem
Post Prodigy
1 month ago
Parchitect Wow, jeez i feel stupid. Thank You 🙏

Let me give this a try tomorow, it will be amazing if i can get this working in powerbi powerquery
- icassiem
  Post Prodigy
  1 month ago
  Parchitect Hi, Is there a way to grab the meta property of file date modified/updated where the client name was mispelled in the filename or date prefixed leaving me with duplicated client file and how i filter on the latest modified/updated date per client in powerquery?
  Thanks

icassiem
Post Prodigy
1 month ago
Good day,

I need help please, i have no tools and only powerbi currently with my F2/F4 Fabric subscription only apporved for august and evlopment targeted for end of November, but now stakeholder wants an mvp to show exec memebers to get the appetite going using an existing report with a full manual operation. The product has an export per client of which i will ingest once i start with fabric and loop, but the stakeholder wants to monthly export per clinet and dump the csv into a folder directlory which is timestamped. The report should pickup theres a new source file and loop through the existing files as the source file for the powerbi.

the option i have is to complie the infor all together for one big file per month and combie or take latest timesamp for 25 lients and paste it and dont say im doing it manually for the next 5 months or can powerquery do this or anyother free source tool untill im up and runningm, the stakeholder is aware this is interim. Or even using powerquery or powershell to merge loop all the the files based on it source being "Mature_client***.csv" to say combine all the mature files to append as one and then in puwerbi powerwuery i transform and clean it, i cant use fabric trial as this is only a month or can it be done with python and i run the notebook manually monthly from my pc or is the a free etl i could use to do this etc

Sorry i know its a crappy ques. I am just thinking out loud because i have no idea and dont want to lose the audience now that theres an apetite until i have fabric up and running

Regards
Dev_Dholakia
Resolver IV
1 month ago
Hi icassiem ,

For an MVP with only Power BI on hand, I'd stick with Power Query's "Combine files from folder" — it's the fastest path and needs zero extra tooling.

Quick approach:
Point Power BI at your root folder (e.g., \\server\share\MatureClients\).
Use Get Data → Folder, then filter the Name column with something like Text.StartsWith([Name], "Mature_client") before hitting Combine.
Power Query auto-generates the sample query + transformation function, so any new CSV dropped into the folder gets picked up on the next refresh.
Do your cleanup/transforms in the sample query — they apply to every file.

If schemas ever drift, pre-combining with PowerShell (Import-Csv | Export-Csv) or Python (pandas.concat) into one monthly file works too, but honestly it's overkill for an MVP.

Caveats worth flagging up front:
Consistent columns across all Mature_client***.csv files — Power Query's combine step is fragile if headers shift.
Stable folder path — moving/renaming the root breaks the query.
Refresh limits — if the folder is local or on an on-prem share, scheduled refresh from the Power BI Service needs an On-premises Data Gateway. OneDrive/SharePoint folders refresh natively without a gateway.
Treat this as a stopgap until Fabric lands — don't over-invest in the plumbing.

Some clarifying Questions:
Where do the CSVs actually live — local drive, network share, SharePoint/OneDrive?
Is the schema guaranteed identical across clients, or do columns vary?
Do you need scheduled refresh from the Service, or is desktop refresh enough for now?

Useful refrences:
Power Query Combine Files
Power Query Import Data From Folder
Service Gateway Onprem

If this got you what you needed, a Kudos and an Accepted Solution mark would be great — it helps others searching for the same thing find the answer quicker.
- icassiem
  Post Prodigy
  1 month ago
  Dev_Dholakia Thank You
  
  the issue is i have no etl to loop on wildcard and place the many client x 5 sources to create a combined 5 sources for powerbi then to transform, as the filenames differ + timestamped but it i can ask the user that places it to name it in a certain manner and overwrite but when clients gest added or dropped of the powerbi build to append/merge will fail. so im hoping theres another method to take all files and merge them into a client source per source type before pwoerbi
  
  i was looking at python or powershell to do execute even once a month but honestly i have no clue with this either. the business cant wait 3-5 months untill the neviorment fabirc is fully up
Prince0011
Solution Sage
1 month ago
Hi,
This isn't a crappy question at all—it's actually a very common situation when building an MVP before the full platform is available.
For the next 4–5 months, I'd keep the solution as simple as possible and avoid introducing another ETL tool unless there's a real need.
Power Query can absolutely handle this. A common approach is:
Save all monthly CSV exports into a single parent folder (including any client subfolders if needed).
In Power BI, use Get Data → Folder.
Use Combine Files to automatically append all CSVs that match your expected schema.
Keep the Source.Name and Folder Path columns so you can extract the client name and timestamp from the filename/folder if required.
If only the latest export per client should be included, Power Query can sort by timestamp and keep the latest file for each client before combining the data.
This approach is dynamic—each month, users simply drop the new CSVs into the folder and refresh the dataset. No need to manually merge files.
If you need a preprocessing step, PowerShell or a small Python script are also good free options, but I'd only go that route if:
The files have inconsistent schemas.
You need complex validation or cleansing before Power BI.
The volume becomes too large for comfortable Power Query refreshes.
Given your timeline, I'd recommend:
Now (MVP): Power BI + Power Query Folder connector.
November (Fabric): Replace the folder source with a Fabric Pipeline/Dataflow Gen2 or Notebook that ingests the client exports into your Lakehouse, while keeping the report logic largely unchanged.
This minimizes rework and gives stakeholders a working solution quickly while providing a clear migration path to Fabric.
One question: are the CSV files all identical in structure, or do some clients produce slightly different columns? That will determine whether the built-in Combine Files experience is sufficient or whether you'll need a bit of custom Power Query logic.
I hope this helps! If you found the suggestion useful, please consider giving it a Like or marking it as the Accepted Solution so it can help others facing a similar interim ETL challenge.

Forum Discussion

ETL Options with files loop (powerquery or free?)

19 Replies

Related content

PowerQuery transformation

Get DP-700 for FREE — Limited Offer

Datatable options

PowerQuery: URL data type option not available

[PowerQuery] add (same) column from another table