Learner... How to refer to a cell in my table?

[EricMametOutloo]

I am trying to remove all columns where the cell in the first row says "Total"

I think the code below might be pretty close but I don't know how to refer to a column to compare it to "Total"
What should I write instead of 
... each ??? = "Total"

let someTable = Table.FromRecords({
    [OrderID = 0, CustomerID = 0, Item = "Total", Price = 130.0],
    [OrderID = 1, CustomerID = 1, Item = "fishing rod", Price = 100.0],
    [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
    [OrderID = 3, CustomerID = 2, Item = "fishing net", Price = 25.0]})
    ,ColumnsToRemove = List.Select(
    Table.ColumnNames(someTable), 
    (ColumnName) => 
        let 
        ColumnValues = Table.Column(someTable, ColumnName),
        ContainsTotal = List.AnyTrue(List.Transform(ColumnValues, each ??? = "Total"))
        in
        ContainsTotal
),
// remove columns
Result = Table.RemoveColumns(someTable, ColumnsToRemove)
in
    Result

 Thanks

[lbendlin · Accepted solution]

You were very close.

let someTable = Table.FromRecords({
    [OrderID = 0, CustomerID = 0, Item = "Total", Price = 130.0],
    [OrderID = 1, CustomerID = 1, Item = "fishing rod", Price = 100.0],
    [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
    [OrderID = 3, CustomerID = 2, Item = "fishing net", Price = 25.0]})
    ,ColumnsToRemove = List.Select(
    Table.ColumnNames(someTable), 
    (ColumnName) => 
        let 
        ColumnValues = Table.Column(someTable, ColumnName),
        ContainsTotal = List.AnyTrue(List.Transform(ColumnValues, each _ = "Total"))
        in
        ContainsTotal
),
// remove columns
Result = Table.RemoveColumns(someTable, ColumnsToRemove)
in
    Result

[Anonymous · Accepted solution]

Hi @EricMametOutloo ,
Thanks for  lbendlin relply.
Another way

let
    Source = Table.FromRows({
        {0, 0, "Total", 130},
        {1, 1, "fishing rod", 100},
        {2, 1, "1 lb. worms", 5},
        {3, 2, "fishing net", 25}
    }, {"OrderID", "CustomerID", "Item", "Price"}),
    FilteredColumns = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), each List.First(Table.Column(Source, _)) <> "Total"))
in
    FilteredColumns

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[dufoq3 · Accepted solution]

Hi @EricMametOutloo, different solution:

let 
    Source = Table.FromRecords({
        [OrderID = 0, CustomerID = 0, Item = "Total", Price = 130.0],
        [OrderID = 1, CustomerID = 1, Item = "fishing rod", Price = 100.0],
        [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
        [OrderID = 3, CustomerID = 2, Item = "fishing net", Price = 25.0]}),
    // Removes columns with "Total" in first row.
    RemovedColumns = [ a = List.PositionOf(Record.ToList(Source{0}), "Total", Occurrence.All),
    b = List.Transform(a, (x)=> Table.ColumnNames(Source){x}),
    c = Table.RemoveColumns(Source, b)
  ][c]
in
    RemovedColumns

[dufoq3 · Accepted solution]

Hi @EricMametOutloo, different solution:

let 
    Source = Table.FromRecords({
        [OrderID = 0, CustomerID = 0, Item = "Total", Price = 130.0],
        [OrderID = 1, CustomerID = 1, Item = "fishing rod", Price = 100.0],
        [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
        [OrderID = 3, CustomerID = 2, Item = "fishing net", Price = 25.0]}),
    // Removes columns with "Total" in first row.
    RemovedColumns = [ a = List.PositionOf(Record.ToList(Source{0}), "Total", Occurrence.All),
    b = List.Transform(a, (x)=> Table.ColumnNames(Source){x}),
    c = Table.RemoveColumns(Source, b)
  ][c]
in
    RemovedColumns

[EricMametOutloo]

I'll put my head round this. Might take me a while because heading for little holiday right now!

Thanks

[dufoq3 · Accepted solution]

Hi, I've added my comments to each step of RemovedColumns

= [ //Record.ToList(Source){0} will select only 1st row from Source and make list from values
    //List.PositionOf fins each position of word "Total" in that list - so if there are for instance 3 occurences it will create list of that positions like {0, 2, 3}
    a = List.PositionOf(Record.ToList(Source{0}), "Total", Occurrence.All),
    //Table.ColumnNames(Source) transform Source to list of its column names and List.Transform(a, ... uses list from a and select this position of column names (so only column names where there is "Total" in 1st row) as a list
    b = List.Transform(a, (x)=> Table.ColumnNames(Source){x}),
    //Uses list from b and removes this columns from Source
    c = Table.RemoveColumns(Source, b)
  ][c]

TIP: if you want to see output of every single step of RemovedColumns just delete [c] and you will see every step as a part of record

[EricMametOutloo]

It's insane... The level of support you supply is just insane!
VERY useful
Thanks a lot

[Anonymous · Accepted solution]

Hi @EricMametOutloo ,
Thanks for  lbendlin relply.
Another way

let
    Source = Table.FromRows({
        {0, 0, "Total", 130},
        {1, 1, "fishing rod", 100},
        {2, 1, "1 lb. worms", 5},
        {3, 2, "fishing net", 25}
    }, {"OrderID", "CustomerID", "Item", "Price"}),
    FilteredColumns = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), each List.First(Table.Column(Source, _)) <> "Total"))
in
    FilteredColumns

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[EricMametOutloo]

VERY nice. Thank You

[EricMametOutloo]

Super! 
Plenty too learn! 
Much appreciated

[lbendlin · Accepted solution]

You were very close.

let someTable = Table.FromRecords({
    [OrderID = 0, CustomerID = 0, Item = "Total", Price = 130.0],
    [OrderID = 1, CustomerID = 1, Item = "fishing rod", Price = 100.0],
    [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
    [OrderID = 3, CustomerID = 2, Item = "fishing net", Price = 25.0]})
    ,ColumnsToRemove = List.Select(
    Table.ColumnNames(someTable), 
    (ColumnName) => 
        let 
        ColumnValues = Table.Column(someTable, ColumnName),
        ContainsTotal = List.AnyTrue(List.Transform(ColumnValues, each _ = "Total"))
        in
        ContainsTotal
),
// remove columns
Result = Table.RemoveColumns(someTable, ColumnsToRemove)
in
    Result

[EricMametOutloo]

In this version, I remove all columns that have "Total" in any row. Not just the first row...