CODEWITHSUNDEEP
excelpowerquery
unlistedgabriel
unlistedgabriel

Reputation: 37

Table.RemoveColumns based on the date

I am looking to insert a remove column step which removes any column where the header (which is a date) is before a certain date (older than X years prior to the current date). I receive a large data dump which is just a list of client names and fees they pay each month from 2012 to today, headed by the month they pay each fee, but as time goes on I don't need the oldest of the data.

So far I have tried producing a list from the headers (based on a previous response from another board member - thankyou @horseyride!) and then removing the columns which dont meet the criteria FROM that list. However it keeps breaking.

This is the latest line in the advanced Editor

#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Calendar Period", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Calendar Period", type text}}, "en-GB")[#"Calendar Period"]), "Calendar Period", "Approved Invoice Amount", List.Sum)

This are the lines i am attempting to create:

"ColumnList" = List.Select(Table.ColumnNames(#"Pivoted Column"), each Text.Contains(_, " ")),,

"Delete Columns"= Table.Transform(#"Pivoted Column", Table.RemoveColumns(#"ColumnList", each {})as table)

in #"Delete Columns"

the Second bit of code I cant seem to get right - that is what I believe it should look like for now. But essentially i want the table to remove any columns where their header (a date) is prior to X amount of years older than todays date.

EDIT - Screenshot of before and after IF the desired cut off was Dec 2012:

Example Data

Thank you in advance

Upvotes: 0

Views: 643

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

Just use following code. For static date:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    final = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source),
                                each try Date.From(_) >= #date(2012,12,1) otherwise true))
in
    final

For dynamic date (older than 3 years prior to the current date):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    final = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), 
            each try Date.From(_) >= Date.AddYears(Date.From(DateTime.FixedLocalNow()),-3)
                 otherwise true))
in
    final

Upvotes: 0

Related Questions