none
Dynamically remove column in a table RRS feed

  • Question

  • Hi,

    How to remove a column where contains some special value?

    A B C D
    1 2 3 5
    2 3 4 ALL

    For example, I want to remove column where it contains the value "ALL". In the above table, it is column D that needs to be removed, transforming to the below table.

    A B C
    1 2 3
    2 3 4

    Does anyone know how to do that?

    Thanks

    Monday, October 5, 2015 9:50 PM

Answers

  • And another way....

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
        Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attrib", "Value"),
        Filter = Table.SelectRows(Unpivot, each ([Value] = "All")),
        Distinct = Table.Distinct(Filter, {"Attrib"}),
        Headers = List.Difference(Table.ColumnNames(Source),Distinct[Attrib]),
        SelectColumns = Table.SelectColumns(Source,Headers)
    in
        SelectColumns

    Bolded part - manually edited.

    Regards :-)

    • Marked as answer by bjzk Tuesday, October 6, 2015 3:51 PM
    Tuesday, October 6, 2015 10:04 AM

All replies

  • Hi,

    Assuming your data is in "Table1", create a blank query, open the advanced editor and paste the following M expression:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Demoted Headers" = Table.DemoteHeaders(Source),
        NestedList = Table.ToColumns(#"Demoted Headers"),
        #"Converted to Table" = Table.FromList(NestedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if List.Contains([Column1],"ALL") then "Remove" else "Keep"),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "Keep")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1"}),
        #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "Custom", each [Column1]{0}),
        
        ColumnsToKeep = #"Added Custom1"[Custom],
        Result = Table.SelectColumns(Source ,ColumnsToKeep)
    in
        Result.

    There are probably more advanced ways to do it in a shorter expression, but the expression above was possible through the UI with only two steps where I edited the formulas.

    • Proposed as answer by Gil RavivMVP Tuesday, October 6, 2015 8:09 AM
    Tuesday, October 6, 2015 8:09 AM
  • And another way....

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
        Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attrib", "Value"),
        Filter = Table.SelectRows(Unpivot, each ([Value] = "All")),
        Distinct = Table.Distinct(Filter, {"Attrib"}),
        Headers = List.Difference(Table.ColumnNames(Source),Distinct[Attrib]),
        SelectColumns = Table.SelectColumns(Source,Headers)
    in
        SelectColumns

    Bolded part - manually edited.

    Regards :-)

    • Marked as answer by bjzk Tuesday, October 6, 2015 3:51 PM
    Tuesday, October 6, 2015 10:04 AM
  • Thanks for the reply. I think Bill Szysz's answer is shorter so I marked his as the answer.
    Tuesday, October 6, 2015 3:51 PM
  • Thanks. It works.
    Tuesday, October 6, 2015 3:51 PM