cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

Highlighted
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
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

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.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors
Top Kudoed Authors