Filter a list of column names by string contained in the header

[GlassShark1]

I'm getting a list of column names from a table like this:

All_Columns = Table.ColumnNames(Source)

In that list of column names, i've got a few that start with "Yr " and then an appropriate number - e.g ("Yr 1", "Yr 2" and so on)

This can be any length, so could stop at "Yr 10" or "Yr 40". 

Is there a way to filter All_Columns down further a create a list of all those that start with "Yr "?

Struggling to find much on applying conditional arguements to a list

Thanks! 🙂

[mahoneypat · Accepted solution]

You can use this syntax with List.Select to keep just those column names.

= List.Select(Table.ColumnNames(Source), each Text.Start(_,2)="Yr")

Regards,

Pat

[GlassShark1]

Perfect! Thanks! 🙂

[amitchandak]

@GlassShark1 , you can unpivot and then you get that in a row and create a list from that data.

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[GlassShark1]

I'm trying to automate this so that the query can find the right columns on it's own. If i pivot on the columns, i first need to select them, and this would need to be hard-coded? This is what i'm trying to avoid...