how to filter data by last week date of the month

[Amardeep100115]

I wish to filter data Source column which has weekly files with date in file name also have column in sheet named "As of Date". file name with date and column date is same. i have combined files in one table but i want to filter file or data by last week date of every month.

Attached snap of the same. 

[v-frfei-msft]

Hi @Amardeep100115 ,

Please check the following steps as below.

1. Split the file names column  to the date column and create a custom of today using DateTime.LocalNow().

2. Creat a new custom column [today]- [file date]

3. Then we can filter the custom to get the result between 1 - 7.

M code for your reference.

let
    Source = Folder.Files("xxx:\xxxx\20190412\files"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each DateTime.LocalNow()),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "today"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Name", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Name.1", "Name.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Name.1", "Extension"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Name.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Name.2.1", "Name.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Name.2.1", type date}, {"Name.2.2", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom", each [Name.2.1]-[today]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Name.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"today", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each [today]-[Name.2.1]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type3", each [Custom] >= 1 and [Custom] <= 7)
in
    #"Filtered Rows"

[Amardeep100115]

It's working, but another query i have to after following these steps. how we can keep one row as a column header form one file and automatically remove other file column header ?  see the below snap

Please advice

[v-frfei-msft]

Hi @Amardeep100115 ,

Where the file date come from? Is it possible to filter the "file date" out directly in the column?

[Amardeep100115]

Every file has AS OF DATE column. its comes with our report, after combining file when i sort it, it shows me duplicate rows of column Heading . and want to know hou we can remove them. also please share PBIX file if possible

[Amardeep100115]

Also i need to filter sheet data by last week of the every month

[v-frfei-msft]

Hi @Amardeep100115 ,

As you said, file name with date and column date is same. So i feel confused of that. Could you please share a sample file to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

[Amardeep100115]

Please find the below link

https://1drv.ms/x/s!Ai3rGPgy20kLvluie40APmXZ1qm2?e=a3oR7B 

[Amardeep100115]

Any hopes on my query