Filtering and dynamically updating list of source CSV files

[toddysm]

Hello,

We have jobs that runs every hour and generate CSV files that follow pattern similar to:

YYYYMMDD-HH_some-static-text.CSV

Where some-static-text is specific to the job that is generating the file. We store those files in an Azure Storage container. We would like to create query in PowerBI that puts the data for all files generated by a specific job in a single table and update this table when new data comes in.

So, we thought that we can achieve this by filtering the list of CSV files in the Query Editor by using Contains constraint but for some reason when we write some-static-text in the Contains field we don't get all the files listed. Here is example with files we have:

Name

aggregates/hourly/20170313-23_conversations-to-ht-count.csv
aggregates/hourly/20170313-23_ht-count.csv
aggregates/hourly/20170313-23_mentions-to-ht-count.csv
aggregates/hourly/20170313-23_users-to-ht-count.csv
aggregates/hourly/20170314-00_conversations-to-ht-count.csv
aggregates/hourly/20170314-00_ht-count.csv
aggregates/hourly/20170314-00_mentions-to-ht-count.csv
aggregates/hourly/20170314-00_users-to-ht-count.csv
aggregates/hourly/20170314-01_conversations-to-ht-count.csv
aggregates/hourly/20170314-01_ht-count.csv
aggregates/hourly/20170314-01_mentions-to-ht-count.csv
aggregates/hourly/20170314-01_users-to-ht-count.csv

When we do Filter -> Text Filters ->Name Contains == "conversations-to-ht-count" in Query Editor (in Power BI Desktop) we get empty list. Any ideas?

[v-sihou-msft · Accepted solution]

@toddysm

Since all your CSV files are under same folder, you can get data from folder.

You will get the metadata of the folder. Add a text filter on Name column to keep expected files only.

"Invoke Custom Function" the transform file from the folder.

Now you just need to remove other columns and expand the "Table" column to get the combined dataset.

Regards,

[toddysm]

Thanks Simon-Hou,

The confusion was that after filtering I wasn't seeing all the files I expected to see. Refreshing from the source solved this. Appreciate the suggestion for Custom Function though - this will come handy