Ingest folder of CSV into dataset?

[pe2950]

I have a legacy application that generates reports daily. 

The reports are fairly basic and provide some stats for the day. I'm attempting to ingest these into BI to build reports from them. 

I'm aware of how to import a single csv however this app will generate a new named file each day / run. 

it also has the ability to send report via email. Unlikely to be helpful. 

easiest way to do this?  

only solution I can think of currently is using power shell or some scripting to iterate the folder. Insert to on prem sql then pull to BI

there must be an easier way? 

we have an on prem data gateway any to parse the files directly into the BI service?

[v-jayw-msft]

Hi @pe2950 ,

Setp1: Connect the folder using Folder connector and open Query Editor.

Step2: Sort the records by create date or modified date.

Step3: Add index column and filter the records according to your need.

Step4: Expand the csv files to tables.

Step5: Delete the name condition in Advance Editor. For example:

let
Source = Folder.Files("xxxxxx"),
#"Sorted Rows" = Table.Sort(Source,{{"Date modified", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] = 1),
#"xxxxxx" = #"Filtered Rows"{[#"Folder Path"="xxxxxx",Name="123.csv"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"xxxxxx"),
Sheet1_Sheet = #"Imported Excel Workbook"{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", Int64.Type}})
in
#"Changed Type"

Best Regards,

Jay

[AlexisOlson]

Ideally, you'd probably want the CSV to be loaded to a database or other data storage so you don't have to parse all of the individual files every time to refresh.

You could probably do this fairly low-code if you use Power Automate to load the CSVs into SQL Server or Dataverse. If you go that route, you can probably find help from their community forums.

[GilbertQ · Accepted solution]

Hi @pe2950 

Here is a blog post which explains how to connect to all the files in a folder. As with your example you point Power BI Desktop at then folder and each and every day it will refresh all the files in this folder.

Loading Data From Folder - (powerbi.tips)