Forum Discussion
Filter column if Source.Name corresponds to Source.Name in last row
Hi flowgistics
Have been very busy lately. So you have all the files in one folder like this? And you want the archive data file and the latest version of xyz? Are you going to use the Date created or the date in filename to identify the latest version?
here is one using Date created
let Source = Folder.Files("folderpath"), #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name],"archive") or [Date created] = List.Max(Source[Date created])) in #"Filtered Rows"
Hello Anonymous , thanks for your reply. With the Table.AddColumn command Power Query seems to go into an endless loop, trying to load more than 1GB into the Power Query (output of the status bar in the right low corner..) before I stopped it. The original files "only" have 36MB.. any ideas why?
*line 1-6 were added by Power BI automatically by choosing folder import, not nice?*
the same loop also happened by trying out your provided sample 😕
But as I read your filterFilename command line, you will always filter after "Filename" hardcoded right? I would need a solution, where this Filename changes twice a month, automatically..?
Thanks for any help.
Hi flowgistics
You M code looks like you are using Sample query, I am not. I read from folder directly and you can see this example is using Excel.Workbook to read a single sheet from a single Excel file, if you read CSV, you can use Csv.Document, it depends...and if you have multiple, you can use Table.Combine
let
Source = Folder.Files("C:\Users\folderpath"),
filterFilename = Table.SelectRows(Source, each Text.Contains([Name], "filename")),
findLatest = Table.SelectRows(filterFilename, each [Date created] = List.Max(filterFilename[Date created])),
Custom1 = Table.PromoteHeaders(Table.Skip( Excel.Workbook(findLatest[Content]{0})[Data]{0},3))
in
Custom1
And yes, my filename is hard coded, what do you mean Filename changes? So maybe you need a parameter to put filename every time when it changes?
Hi Anonymous, first: sorry for the late reply.
I'm still struggling with Power Query, found a solution in DAX but the problem is that the model slows down every month caused by additional added data..
Once again: I have .txt input data, that get's reported twice a month into one folder (-> changing filenames, e.g. for August: "xyz_20210801_0600" and "xyz_20210815_0600") with current data, but I'm only interested in the latest report. Additionally I have archive data from 2017 until 2020 (with identical columns).
Two questions:
- how would you realize a parameter to be able to only load the latest input data rows into the model?
- is it possible to handle the archive data file with folder import as well or is that too complicated to realize and go for a standard file import for this file instead?
thanks for all answers!
Hi flowgistics
I don't seem to understand your question
Two questions:
- how would you realize a parameter to be able to only load the latest input data rows into the model? Do you mean the rows in your latest txt file or you mean the latest version as of your August in the sample?
- is it possible to handle the archive data file with folder import as well or is that too complicated to realize and go for a standard file import for this file instead? What do you mean archive data file?
For example, you read a folder, you can use the columns Date created or others to identify the latest version OR extract the date in your filename as shown in your sample to compare and filter to the files you need
Hi Anonymous
For better understanding I've created a small example of my handled data:
Source.Name ProductionID ProductionTime ProductionAmount Index archiveData 111111 01.04.2017 06:35 59500 1 archiveData 111112 01.04.2017 07:20 59700 2 … … … … … archiveData 112879 01.04.2020 04:50 60300 90013 xyz_20210715 121111 01.04.2020 06:30 60000 90014 xyz_20210715 121112 01.04.2020 06:44 59000 90015 … … … … … xyz_20210715 131456 15.07.2021 05:10 61500 125627 xyz_20210801 121111 01.04.2020 06:30 60000 125628 xyz_20210801 121112 01.04.2020 06:44 59000 125629 … … … … … xyz_20210801 141378 01.08.2021 04:57 60200 130235 xyz_20210815 121111 01.04.2020 06:30 60000 130236 xyz_20210815 121112 01.04.2020 06:44 59000 130237 … … … … … xyz_20210815 151621 15.08.2021 05:16 61000 135365 So at the moment the folder import loads archive data rows from 2017 until the morning of the 1st April 2020, as well as reports created twice a month with the current data from the 1st April 2020 until the morning the report was created..
In my model, I want to load archive data as well as the rows of the latest available report which would be "xyz_20210815" at the moment.
Hope that explanation helps?