cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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 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"

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Selecting first and last column only

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 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Selecting first and last column only

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 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Helper I
Helper I

Re: Selecting first and last column only

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

Thanks.

Memorable Member
Memorable Member

Re: Selecting first and last column only

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 

 

Highlighted
Memorable Member
Memorable Member

Re: Selecting first and last column only

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

 

 

Highlighted
Helper I
Helper I

Re: Selecting first and last column only

@Rocco_sprmnt21 Thank you!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.