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"
Hi flowgistics
The code should be like this, but as you said, it is not your case, so you need to provide the logic how you define the latest
Table.AddColumn(#"LastStep", "TrueFalse", each if [Index] = List.Max(#"LastStep"[Index]) then 1 else 0)
here is one sample of mine
let
Source = Folder.Files("C:\Users\----"),
filterFilename = Table.SelectRows(Source, each Text.Contains([Name], "Filename")),
findLatest = Table.SelectRows(filterFilename, each [Date created] = List.Max(filterFilename[Date created])),
....
in
...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 Custom1And 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!
Syndicate_AdminAdministrator
Hello @Vera_33 , 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.
