Finding Dates in Column Headers and Unpivoting them in Power Query

[programsam]

Not even sure where to start with this one but have a file that has several column headers, some with MMMM/YYYY in them. Because the file refreshes each week with new information from a folder where new data is appended to existing, I'd like to be able to unpivot the columns where dates are detected.

I would assume using something such as Date.From with some type of IF/THEN statement to look for "MMMM/YYYY" and then unpivoting but I'm unsure of the correct syntax. Is this even possible with Power Query?

 

[RoryA]

If there is a fixed set of columns not to unpivot, you could simply use that with Unpivot other columns. If not, you can do something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // get a list of the columns that look like dates
    DateColumns = List.Select(Table.ColumnNames(Source), each try Value.Is(Date.From(_), type date) otherwise false),
    // use that list to unpivot
    #"Unpivoted Columns" = Table.Unpivot(Source, DateColumns, "Attribute", "Value")
in
    #"Unpivoted Columns"

 

[programsam]

If there is a fixed set of columns not to unpivot, you could simply use that with Unpivot other columns. If not, you can do something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // get a list of the columns that look like dates
    DateColumns = List.Select(Table.ColumnNames(Source), each try Value.Is(Date.From(_), type date) otherwise false),
    // use that list to unpivot
    #"Unpivoted Columns" = Table.Unpivot(Source, DateColumns, "Attribute", "Value")
in
    #"Unpivoted Columns"

Hi Rory, there won't be a fixed set of columns because each month, as a new month is added it will add an extra column. Is there a way to count the # of columns, then look for dates and then unpivot?

Appreciate the prompt reply.

 

[RoryA]

I said if there is a fixed list of columns not to unpivot. So you might have 5 fixed columns followed by a varying number of date columns. You can then select the fixed ones and use Unpivot Other columns, so it will unpivot anything that isn't the columns you selected.

The code I posted will check all the column names and unpivot any that look like dates.

 

[programsam]

I said if there is a fixed list of columns not to unpivot. So you might have 5 fixed columns followed by a varying number of date columns. You can then select the fixed ones and use Unpivot Other columns, so it will unpivot anything that isn't the columns you selected.

The code I posted will check all the column names and unpivot any that look like dates.

Thank you, this worked masterfully. If there were an existing column that I also wanted to unpivot along with these called "MTD", how would this be accomplished?

 

[RoryA]

You can amend the select part to:

List.Select(Table.ColumnNames(Source), each _ = "MTD" or (try Value.Is(Date.From(_), type date) otherwise false))