Dynamically Remove Columns In Power Query By Header Dates

Question

I have a query table of data that has been merged in Power Query. The result is monthly percentages for each person for two years that looks like this. I would like to dynamically remove months from the table when the month is over. So, starting August 1st, July would be removed from the power query table when I refresh it.

Merged Query Table:

Name	Jul-21	Aug-21	Sept-21
Bob	.10	.20	.10
Amy	.30	.40	.10

I've looked through StackOver, and there is one post that is similar to what I want to accomplish: Table.RemoveColumns based on the date

The code suggested in that post to remove column headers older than three years was:

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

I went into the Advanced Editor in Power Query to try to adjust it to what I was trying to do, but the code does not work and no columns are removed. This is even before adjusting the code to not remove the current month.

let
    Source = Table.Combine({Merge1, Merge2}),
    final = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), 
            each try Date.FromText(_) >= Date.From(DateTime.FixedLocalNow())
                 otherwise true))
in
    final

I have tried both each try Date.From(_) and each try Date.FromText(_), neither of which removed any columns. I have not altered the Date.From(DateTime.FixedLocalNow()) part of the equation in any of my tests.

I have tried to search for online resources to understand how this code works as well as used Microsoft's support site to learn about Table.SelectColumns and List.Select, but I have yet to find a good resource that can break down how to understand the above code, so it makes it difficult for me to understand what changes I need to make to get it work.

So far, I understand that the List.Select is determining the columns that will be included in the Table.SelectColumns function. List.Select is creating a list of all the column headers and then measuring their text (transformed by the Date.FromText(_) function into a date) against the current date using the each try and the otherwise is catching all the non-date headers and making sure they are included as well.

But I don't understand why the equation isn't removing any the past date columns or how to adjust the function (once it works) to not remove the current month. I would appreciate any assistance with both of these questions. I can share a link to the full spreadsheet if that would be helpful.

horseyride · Accepted Answer · 2022-04-18 16:43:24Z

1

How about

 Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), 
   each try 
   Date.StartOfMonth(Date.FromText(Text.Start(_,3)&"/01/"&"20"&Text.End(_,2)))
   >= Date.StartOfMonth(Date.From(DateTime.FixedLocalNow()))
   otherwise true))

edited Apr 18, 2022 at 16:43

answered Apr 18, 2022 at 10:30

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SaraT Over a year ago

Thanks for responding. I have included a picture in the post. It's the first situation. I've tried that code, but only the dates between January, 2022 and March, 2022 are being removed. The Jul-21 to Dec-21 dates are remaining. My columns do have 1.2 next to the header name instead of ABC123. I can provide a shared link to the file in OneDrive if that would be helpful.

SaraT Over a year ago

Solved. Thank you so much! My problem was that my date format was storing Jan-21 as Jan 21, 2022.

Collectives™ on Stack Overflow

Dynamically Remove Columns In Power Query By Header Dates

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related