Remove Columns from List if Header Name begins with any value from another List

[jordtee]

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 data includes the text "BA", the column needs to stay; the tricky part is also getting the column with the same first digit in the header to stay. I.E. The text "BA" is found in column "2 INCL." and so I need columns "2 INCL." and "2 BREAKOUT $" to remain. These "column pairs" go up to 10, so really the first two digits. 

I have successfully created a List of the conditionally needed cells with:

NumberColumns = List.Select(
Table.ColumnNames(#"Reordered Columns"),
each Text.StartsWith(_, "1") or Text.StartsWith(_, "2") or Text.StartsWith(_, "3") or Text.StartsWith(_, "4") or Text.StartsWith(_, "5") or Text.StartsWith(_, "6") or Text.StartsWith(_, "7") or Text.StartsWith(_, "8") or Text.StartsWith(_, "9") )

I have successfully created a List of the header beginnings for columns which contain "BA" with:

NumCol_w_BA = List.Transform(
List.Select(
NumberColumns,
each List.Contains(Table.Column(#"Reordered Columns", _), "BA")), each Text.Start( _, 1))

I haven't been able to find a way to return a list of column headers from the list of conditionally needed columns (NumberColumns) which don't start with any of the numbers from column headers I want to keep (NumCol_w_BA). I tried something like this to no avail (I think Text.StartsWith is looking for only one value to check against):

StartWithKeepNum = List.Select(
    NumberColumns, 
    each Text.StartsWith(NumberColumns, NumCol_w_BA)
)

FULL CODE: 

let
Source = Excel.CurrentWorkbook(){[Name="Table47"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Item", type text}, {"Qty", Int64.Type}, {"Labor EA", Int64.Type}, {"Labor EXT", Int64.Type}, {"Misc EA", Int64.Type}, {"Misc EXT", Int64.Type}, {"Buy from#(lf) Vendor/Quote", type text}, {"1 #(lf)INCL.", type text}, {"1 #(lf)BREAKOUT $", Int64.Type}, {"2 #(lf)INCL.", type text}, {"2 #(lf)BREAKOUT $", Int64.Type}, {"3 #(lf)INCL.", type text}, {"3 #(lf)BREAKOUT $", Int64.Type}, {"4 #(lf)INCL.", type text}, {"4 #(lf)BREAKOUT $", type any}, {"5 #(lf)INCL.", type text}, {"5 #(lf)BREAKOUT $", type any}, {"6 #(lf)INCL.", type text}, {"6 #(lf)BREAKOUT $", Int64.Type}, {"7 #(lf)INCL.", type any}, {"7 #(lf)BREAKOUT $", type any}, {"8 #(lf)INCL.", type any}, {"8 #(lf)BREAKOUT $", type any}, {"9 #(lf)INCL.", type any}, {"9 #(lf)BREAKOUT $", type any}, {"10 #(lf)INCL.", type any}, {"10 #(lf)BREAKOUT $", type any}}),
FilterBAonly = Table.SelectRows(#"Changed Type", each [#"1 #(lf)INCL."] = "BA" or [#"2 #(lf)INCL."] = "BA" or [#"3 #(lf)INCL."] = "BA" or [#"4 #(lf)INCL."] = "BA" or [#"5 #(lf)INCL."] = "BA" or [#"6 #(lf)INCL."] = "BA" or [#"7 #(lf)INCL."] = "BA" or [#"8 #(lf)INCL."] = "BA" or [#"9 #(lf)INCL."] = "BA" or [#"10 #(lf)INCL."] = "BA"),
#"Reordered Columns" = Table.ReorderColumns(FilterBAonly,{"Buy from#(lf) Vendor/Quote", "Category", "Item", "Qty", "Labor EA", "Labor EXT", "Misc EA", "Misc EXT", "1 #(lf)INCL.", "1 #(lf)BREAKOUT $", "2 #(lf)INCL.", "2 #(lf)BREAKOUT $", "3 #(lf)INCL.", "3 #(lf)BREAKOUT $", "4 #(lf)INCL.", "4 #(lf)BREAKOUT $", "5 #(lf)INCL.", "5 #(lf)BREAKOUT $", "6 #(lf)INCL.", "6 #(lf)BREAKOUT $", "7 #(lf)INCL.", "7 #(lf)BREAKOUT $", "8 #(lf)INCL.", "8 #(lf)BREAKOUT $", "9 #(lf)INCL.", "9 #(lf)BREAKOUT $", "10 #(lf)INCL.", "10 #(lf)BREAKOUT $"}),
NumberColumns = List.Select(
Table.ColumnNames(#"Reordered Columns"),
each Text.StartsWith(_, "1") or Text.StartsWith(_, "2") or Text.StartsWith(_, "3") or Text.StartsWith(_, "4") or Text.StartsWith(_, "5") or Text.StartsWith(_, "6") or Text.StartsWith(_, "7") or Text.StartsWith(_, "8") or Text.StartsWith(_, "9") ),
NumCol_w_BA = List.Transform(
List.Select(
NumberColumns,
each List.Contains(Table.Column(#"Reordered Columns", _), "BA")), each Text.Start( _, 1)),
StartWithKeepNum = List.Select(
NumberColumns, each Text.StartsWith(NumberColumns, NumCol_w_BA)
)
in
StartWithKeepNum

[jbwtp · Accepted solution]

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
    Output

Kind regrds,

John

[jordtee]

@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 false

This 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

[jordtee]

Thanks @jbwtp ! Being able to compare a list to another list with your "SelectColumns" code worked beautifully. Hopefully this helps others too.

[AlexisOlson]

Another method for this condition:

each List.Contains({"0".."9"}, Text.Start(_,1))

[jordtee]

This method for this condition also worked for me. Thanks @AlexisOlson