Forum Discussion

kentlee65's avatar
kentlee65
New Member
6 years ago
Solved

Selecting first and last column only

I have a table from excel spreadsheet and I'm trying to keep only the first and last columns, as I will be adding to it monthly. I've tried the information from this post but I cannot seem to make it...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi

    You'd have to add the following step:

     

    let
    Source = Excel.Workbook(File.Contents("\\xxxx\DoIT\SpiUsers1\KENT.LUTTRELL\Personal Items\Project Files\IES Docs\IES DB Data\IES Table Growth.xlsx"), null, true),
    #"Table Growth_Sheet" = Source{[Item="Table Growth",Kind="Sheet"]}[Data],
    #"Removed Blank Rows" = Table.SelectRows(#"Table Growth_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Blank Rows",5),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Column7", "Column8"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    KeepFirstAndLastColumn = 
        Table.SelectColumns(
           #"Transposed Table",
           { List.First ( Table.ColumnNames ( #"Transposed Table" ) ),
             List.Last ( Table.ColumnNames ( #"Transposed Table" ) ) } 
          )
in
    KeepFirstAndLastColumn
ImkeF's avatar
ImkeF
Community Champion
6 years ago

Hi

You'd have to add the following step:

 

let
    Source = Excel.Workbook(File.Contents("\\xxxx\DoIT\SpiUsers1\KENT.LUTTRELL\Personal Items\Project Files\IES Docs\IES DB Data\IES Table Growth.xlsx"), null, true),
    #"Table Growth_Sheet" = Source{[Item="Table Growth",Kind="Sheet"]}[Data],
    #"Removed Blank Rows" = Table.SelectRows(#"Table Growth_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Blank Rows",5),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Column7", "Column8"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    KeepFirstAndLastColumn = 
        Table.SelectColumns(
           #"Transposed Table",
           { List.First ( Table.ColumnNames ( #"Transposed Table" ) ),
             List.Last ( Table.ColumnNames ( #"Transposed Table" ) ) } 
          )
in
    KeepFirstAndLastColumn
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    ImkeF is there any way to have the fist 3 and the last 3 columns? 

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      yes, you can.

      In general firts N and last M, in this way:

       

         

 KeepFirstNAndLastMColumns = 
        Table.SelectColumns(
           #"Transposed Table",
           List.FirstN ( Table.ColumnNames ( #"Transposed Table" ),N )&
             List.LastN ( Table.ColumnNames ( #"Transposed Table" ),M ) 
          )
in
    KeepFirstNAndLastMColumns

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        using List.RemoveRange

         

        Cols=Table.ColumnNames ( #"Changed Type" ),
last=3,first=2,
selectedCols=List.RemoveRange(Cols,first, List.Count(Cols)-(last+first)),
     KeepFirstNAndLastMColumns = 
        Table.SelectColumns(
           #"Changed Type",
           selectedCols 
          )
in
    KeepFirstNAndLastMColumns