Over 45+ folder structures to pull .txt/.csv file from via Power Query

[Karolina411]

INTRO:

My mission is to pull the most recent file from various folders that are sent from the PAYOR and organize them by the most recent date per payor. Then I must pull the values for TOTALS PAID by the INSURER and form a line graph, etc.

1st I am pulling .txt files  from a Folder :

\\emirprd\edwdata\ELIGIBILITY\HAP\HFALLG

The folder structure is different in every folder  I choose:

2) I then choose the Content Column and select the columns I want from the MOST RECENT FILE

3)Then I filter and then open the columns in that file Attributes Column

The question is how to pull data from over 40 different folder file paths (some with many files) in an efficient way?  Has this been done before?

[Karolina411]

I am adding on a powerbi.com related issue to this problem.  When I upload it to the cloud it will not accept it.  I checked the data sources and get this : 

My cloud error is this: 

That being said it is tough to work out how to handle this one?  the files are being pulled form a path that can be refreshed.

[Karolina411]

I applied the CUSTOM Filter (update based on dates after tx date) so hopefully this will ensure it updates when new files enter the folders.

[Karolina411]

So even if I created a NEW date column from the list of files--just by using a FILTER it will not update my list

[lbendlin]

it will update every time you refresh your dataset semantic model.

[Karolina411]

Here is what they data looks like! Thank you!

[lbendlin]

Fair enough.  In that view add a filter on the Created Date.

[Karolina411]

@lbendlin  the import has a date column already--I just do not want to pull in this many files and those in charge of the file structure will not archive old files in a different folder. I want to pull files by > than date then also create a Dax Formula that makes the data red when the files have not pulled in by a certain date.

[lbendlin]

the import has a date column already

Which one would that be?

[lbendlin]

sounds like a standard process.  What have you tried and where are you stuck?

[Karolina411]

So I am getting the standard error message about 'hand authored ' queries therefore it will not upload to the cloud

[lbendlin]

you should use \\emirprd\edwdata as your only data source.

can you show some more of the Power Query script?

[Karolina411]

Sure--I must connect to 48 folders to pull the latest .csv file

ex:  Step 1 = add Folder

Step 2 = set up custom filter so it pulls after October (3 months prior from today)

[lbendlin]

- connect to the main share only, not to the subfolders

- have a list of all the subfolders

- iterate through them and fetch a list of all the files in these folders that match your criteria

- fetch the files as needed.

[Karolina411]

Thank you for your reply! The issue is how to just import .txt files > than a certain date.  The dates are found in the name itself and the column  pulled in Power Query.  How can just have a refresh on file names > than , say , November 23rd, 2023?  Thank you!

[lbendlin]

create a new date column from the file name, then apply your filter on that new column.

[Karolina411]

I must test that as I found if I do any filtering on the date column then when a new file enters the folders it does not update my slicer to show the latest file as it is obeying the filter placed.