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:

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.

CodePudding user response：

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))