Forum Discussion
Over 45+ folder structures to pull .txt/.csv file from via Power Query
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?
16 Replies
sounds like a standard process. What have you tried and where are you stuck?
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!
create a new date column from the file name, then apply your filter on that new column.
So I am getting the standard error message about 'hand authored ' queries therefore it will not upload to the cloud
you should use \\emirprd\edwdata as your only data source.
can you show some more of the Power Query script?
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.
the import has a date column alreadyWhich one would that be?
Here is what they data looks like! Thank you!
Fair enough. In that view add a filter on the Created Date.
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
it will update every time you refresh your
datasetsemantic model.
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.
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.
