Forum Discussion

flowgistics's avatar
flowgistics
Frequent Visitor
5 years ago
Solved

Filter column if Source.Name corresponds to Source.Name in last row

Hi everyone,

I'm not looking through M-code that well yet and hope that somebody can help me with my problem:

 

I'm importing .txt files through folder import, but only want the latest report to be considered in Power BI. So I thought to create a true/false column, if each Source.Name is equal to the last row's Source.Name and filter this column afterwards. Thatfore I would already have an index column if necessary..

 

I tried something similar to Table.AddColumn(#"LastStep", "TrueFalse", each if [Index] = List.Max([Index]) then 1 else 0) which doesn't work and is also not 100% my case.

 

It would additionally be great to add one exception Source.Name like "Produktqualitaet_archive" to be set true as well.

 

Thanks a lot for your replies in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"

     

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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
    ...
    • flowgistics's avatar
      flowgistics
      Frequent Visitor
      5 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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?

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      5 years ago

      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?

       

      DiosAUT_0-1627979409455.png

      *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.

  • flowgistics's avatar
    flowgistics
    Frequent Visitor
    5 years ago

    are my descriptions not comprehensible or is the problem too tricky that no solution can be found? 🤔