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.

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

August Community Highlights

Check out a full recap of the month!

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.

Top Solution Authors