Forum Discussion
Remove Columns from List if Header Name begins with any value from another List
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
Memorable MemberHi 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
Output
Kind regrds,
John
jbwtp I was able to utilize my original code along with your code for "SelectColumns":
SelectColumns = List.Select(ConditionalColumns, each not List.Contains(ContainsBA, Text.Start(_, 1))),This let me select from a list based on if it started with the a first digit from another list.
However, I can't seem to get your code for "ConditionalColumns" to return anything. I like the simplicity versus my multiple OR statements.
ConditionalColumns = List.Select(Table.ColumnNames(#"Changed Type"), each try Number.From(_)>0 otherwise false),Do you have any input on how to select column names that start with a number like your code?
- jbwtp
Hi 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
Another method for this condition:
each List.Contains({"0".."9"}, Text.Start(_,1))This method for this condition also worked for me. Thanks AlexisOlson
