Selecting first and last column only

[kentlee65]

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 work. Below is the query currently before I'm ready to select those columns. Suggestions are helpful.

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")
in
    #"Transposed Table"

[ImkeF · Accepted solution]

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]

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

Thanks.

[Anonymous]

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]

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

[Anonymous]

@Anonymous Thank you!