Forum Discussion
Solved
Remove Columns from List if Header Name begins with any value from another List
I am trying to remove columns which aren't relevant from a source table in Power Query. The first 8 columns are always needed, but there are 20 columns that are conditionally needed: If the column da...
Hi jordtee,
Try something like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBIUcgdsJJx+pEKyUmJkKUOjlCaHQEUpSUlIRdEqYNpCg5ORmXNNSkWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Another = _t, #"1" = _t, #"1.1" = _t, #"2" = _t, #"2.1" = _t, #"3" = _t, #"3.1" = _t, #"4" = _t, #"4.1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Another", type text}, {"1", type text}, {"1.1", type text}, {"2", type text}, {"2.1", type text}, {"3", type text}, {"3.1", type text}, {"4", type text}, {"4.1", type text}}), ConditionalColumns = List.Select(Table.ColumnNames(#"Changed Type"), each try Number.From(_)>0 otherwise false), ContainsBA = List.Accumulate(ConditionalColumns, {}, (a, n)=> if List.Contains(Table.Column(#"Changed Type", n), "BA") then a & {n} else a), SelectColumns = List.Select(ConditionalColumns, each not List.Contains(ContainsBA, Text.Start(_, 1))), Output = Table.RemoveColumns(#"Changed Type", SelectColumns) in OutputKind regrds,
John
jbwtp
Memorable Member
Memorable MemberHi jordtee,
try using this
each try Number.FromText(Text.Start(_,1))>0 otherwise falseThis gives a bit more clarity to PBI on what we are trying to do.
The main idea here (in the absence of a stock function) to try to convert a first character to a number (column names are always text type). If it works (and assuming the column does not start with 0 otherwise just use -1) this means that the first character is number, otherwise the Number.FromText will through an error which will be intercepted by the otherwise part of the function. From this point of view try...otherwise work like if...else.
Cheers,
John
AlexisOlson
Super User
Super UserAnother method for this condition:
each List.Contains({"0".."9"}, Text.Start(_,1))
This method for this condition also worked for me. Thanks AlexisOlson
