Get data from file where named range is in the future

[Anonymous]

Hello,

i want to load only the data from files where the named Range "Ablaufdatum" - which is a date - is not expired (so "Ablaufdatum is in the future).

I currently have the list of all Excels, which I want to combine and transform with a Transform Sample File. I tried the follwing, but CurrentWorkbook is limited. (Excel.CurrentWorkbook(){[Name="Ablaufdatum"]}[Content]). 

Do you have an idea, how I can only get data from Excel Workbooks where the Named Range "Ablaufdatum" is still in the future?

[Anonymous]

I now tried to use this line in the M code: 

= Table.AddColumn(ShowOnlyMaintFolder, "Custom", each functionGetMyRange([Folder Path]&[Name]))

in addition with this function:

let GetNamedRange=(MyFile)=>

let
Source = Excel.Workbook(File.Contents(MyFile), null, true){[Name="Ablaufdatum"]}[Content],
#"MyDate" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
OnlyDate = #"MyDate"{0}[Column1]
in
OnlyDate

in GetNamedRange

This is the error:

[Anonymous]

Thanks for the reply! Altough the Ablaufdatum is not in the file name! The Ablaufdatum is a named range somewhere in the Excel file. This cell (with date as a value) is always in a different spot, which is why i used named ranges. 

So i have a value in every Excel Sheet ("Ablaufdatum") as a named range, which is located sometimse in Cell A2, somtimes B3 and so on. It that date is expired, I dont want to include this excel file in my Query!

Hope it is clear!

[mussaenda]

Hi @Anonymous,

It is better if you will get the excel coming from a folder connector instead of excel connector.

the filter the filename or sourcename like Text.Contains(Text.Upper([ColumnName]) , "ABLAUFDATUM")

in that case, it will read all the files with ablafdatum within and it will disregard consider all letter cases.

Hope this Helps