Pick up latest file (Excel) in folder and OPEN

[Anonymous]

Dear all,

There are many topics around here discussing opening the latest file in a folder (TOP 1 ROW), so let's not get into that.
My problem is that the latest file is an Excel file (xlsx) and the next step of my script - after actually getting the latest file - is opening (expanding) the content.
With this expansion comes a step displaying the full path to the file.

Now, when I refresh, my script fails because the 'new latest' file has a whole other filename. I should be able to create this path dynamically.

Hope my explanation is clear enough. I will try to add a screenshot.

Thanks in advance!

[AlexisOlson]

Can you paste in the M code for your query from the Advanced Editor window?

[Anonymous]

Hi Alexis,

Can you help based on the Advanced Editor script? Is the problem clear?

Many thanks in advance,

[AlexisOlson · Accepted solution]

It looks like you drilled down on the binary cell.

Instead of

path = #"Renamed Columns1"{[#"Folder Path"="M:\SALES\SELL-OUT\2_Customer data\C**TUS\2018\",Source.Name="20181119 file without errors.xlsx"]}[Content],

try this

path = #"Renamed Columns1"{0}[Content],

It should be the same thing (the 0th/1st row of your table, Content column). Instead of referencing the (single) row by name, you reference it by index.

[Anonymous]

Alexis, you did it!! (was it really so easy ) Thanks!

[Anonymous]

Please find the script below. Many thanks for your feedback.

The problem is the first code block, should have this '20181119 file without errors.xlsx' be dynamic.

Let's say the new name of the file is '20181215 endyear sales.xlsx'

    path = #"Renamed Columns1"{[#"Folder Path"="M:\SALES\SELL-OUT\2_Customer data\C**TUS\2018\",Source.Name="20181119 file without errors.xlsx"]}[Content],
    #"Imported Excel" = Excel.Workbook(path),

let
    Source = Folder.Files("M:\SALES\SELL-OUT\2_Customer data\C**TUS\2018"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.EndsWith([Name], ".xls") or Text.EndsWith([Name], ".xlsx")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Kept First Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from 2018", each #"Transform File from 2018"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    path = #"Renamed Columns1"{[#"Folder Path"="M:\SALES\SELL-OUT\2_Customer data\C**TUS\2018\",Source.Name="20181119 file without errors.xlsx"]}[Content],
    #"Imported Excel" = Excel.Workbook(path),
    Ventes_Sheet = #"Imported Excel"{[Item="Ventes",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(Ventes_Sheet,3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Num art.", type any}, {"Lib art.", type text}, {"Etat achat", type text}, {"Etat vente", type any}, {"Code ast", type any}, {"Code liv", type any}, {"Prioritaire", type text}, {"Marque", type text}, {"Activité", type any}, {"Lib act.", type text}, {"Ref Fourn.", type any}, {"Code EAN", type any}, {"PCONS", type any}, {"Cumul ventes", type any}, {"CA cumulé", type any}, {"B-E", type any}, {"HDIEK", type any}, {"BASCH", type any}, {"HHOW", type any}, {"BEREL", type any}, {"LAL", type any}, {"WIN", type any}, {"IECHT", type any}, {"MERSCH", type any}, {"INGELD", type any}, {"KAYL", type any}, {"PETAN", type text}, {"REMICH", type any}, {"RED II", type any}, {"HOWALD", type any}, {"MARN", type any}, {"SCHELEC", type any}, {"CATHOME", type text}, {"LUXGAR", type text}, {"ES/ALZ", type text}, {"SCHIFF", type text}, {"ETTEL", type text}, {"BETT", type text}, {"MERL", type any}, {"LIMPER", type text}, {"BONNEV", type text}, {"DIFFER", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Marque] = "CE**AR-LINE")),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"Num art.", "Lib art.", "Etat achat", "Etat vente", "Code ast", "Code liv", "Prioritaire", "Marque", "Activité", "Lib act.", "Ref Fourn.", "Code EAN", "PCONS", "Cumul ventes", "CA cumulé"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Store"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "KeyVentes", each Text.From([Store]) & Text.From([Code EAN])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Code EAN", type number}, {"Ref Fourn.", type number}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Value", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2"," ","",Replacer.ReplaceText,{"KeyVentes"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value",{{"PCONS", type number}, {"CA cumulé", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type3", "Custom", each [CA cumulé]/[PCONS]),
    #"Renamed Columns2" = Table.RenameColumns(#"Added Custom1",{{"Custom", "CA cumulé qty"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Cumul ventes", Int64.Type}})
in
    #"Changed Type4"