Power BI picking latest file issue

Saxon10 , What is the code you are using to get the latest

also check -https://medium.com/@wkrzywiec/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4bff711

Hey Saxon10 

 

The error is not clear from the image along with the steps. I have tried replicating it, and it works for me:

let
    Source = Folder.Files("Enter the Path"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table Column1", each [Column1] <> null),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each [AB] <> "AB"),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [AB] <> 133)
in
    #"Filtered Rows2"

 

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

Hey Saxon10 ,

 

The code does the following:

 

1. Go to the folder
2. Read all the files
3. Filters out the latest file based on the date mentioned in the file name
4. Gives the output in the desired format

 

 

let
  Source = Folder.Files(
      "ENTER FOLDER LOCATION"
    ),
  #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
  #"Renamed Columns1" = Table.RenameColumns(#"Filtered Hidden Files1", {"Name", "Source.Name"}),
  #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Content"}),
  #"Extracted Date from SourceName" = Table.AddColumn(
      #"Removed Other Columns", 
      "Date", 
      each 
        let
          splitSourceName = Splitter.SplitTextByDelimiter("_EXTRACT_", QuoteStyle.None)(
              [Source.Name]
            ),
          splitsplitSourceName1 = Splitter.SplitTextByDelimiter("_", QuoteStyle.None)(
              splitSourceName{1}?
            )
        in
          Text.Combine(
              {
                Text.Middle([Source.Name], 13, 2), 
                Text.Combine(List.Transform(splitsplitSourceName1, each Text.Start(_, 2)), "-")
              }
            ), 
      type date
    ),
  #"Filtered Rows" = Table.SelectRows(
      #"Extracted Date from SourceName", 
      let
        latest = List.Max(#"Extracted Date from SourceName"[Date])
      in
        each [Date] = latest
    ),
  Content = #"Filtered Rows"{0}[Content],
  #"Imported Excel" = Excel.Workbook(Content),
  DATA_Sheet = #"Imported Excel"{[Item = "DATA", Kind = "Sheet"]}[Data],
  #"Removed Top Rows" = Table.Skip(DATA_Sheet, 1),
  #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars = true])
in
  #"Promoted Headers"

 

 

 

Hope this helps.

 

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter