Forum Discussion

twister8889's avatar
twister8889
Helper V
6 years ago
Solved

Remove all columns since one specific column

Hi guy,

I need to remove all columns of my table, since one specific name column. In my example, I need to remove all columns after the New columns (Column D)

 

I need to this in power query, the column: New Columns, sometimes can appear in the excel and sometimes this column doesn't exist in the excel, so I need this validation dynamically

 

The expected result is, only columns (A,B,C):

Country | City | Zip

 

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello twister8889 

     

    was this stated in your first post? No. 

    Please always state what exactly you need

    this code should fit your second requirement

    let
	Source = #table
	(
		{"Country","Place","Zip","New Columns 1","State"},
		{
			{"A","43466","","",""}
		}
	),
	ColumnName = "New Column",
	DeleteColumns = if List.IsEmpty(List.Select(List.Transform(Table.ColumnNames(Source), each Text.Contains(_, ColumnName)) ,each _ = true)) then Source else 
		Table.RemoveColumns(Source, List.Difference(Table.ColumnNames(Source), List.FirstN(Table.ColumnNames(Source), each not(Text.Contains(_,  ColumnName))) ))
in
	DeleteColumns

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

9 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello twister8889 

     

    analyse the code ... this does the trick

    let
	Source = #table
	(
		{"Country","City","Zip","New Columns","State"},
		{
			{"A","43466","","",""}
		}
	),
	ColumnName = "New Columns",
	DeleteColumns = if List.IsEmpty(List.Select(Table.ColumnNames(Source),each _ = ColumnName)) then Source else 
		Table.RemoveColumns(Source, List.LastN(Table.ColumnNames(Source), each _ <> ColumnName)&{ColumnName})
in
	DeleteColumns

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • twister8889's avatar
      twister8889
      Helper V
      6 years ago

      Jimmy801 Thank you so much, this solution is almost that I need 🙂

       

      I'm trying to insert the wildcard in the ColumnName because I can have the New Columns 1, New Columns 2, So I need to remove all column after the first New Columns

       

      It's necessary the wildcard: I don't know how to do this in your solution,  any Text.StartWith(_"New Columns") or Text.Contain(_,"New Columns)

       

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello twister8889 

         

        was this stated in your first post? No. 

        Please always state what exactly you need

        this code should fit your second requirement

        let
	Source = #table
	(
		{"Country","Place","Zip","New Columns 1","State"},
		{
			{"A","43466","","",""}
		}
	),
	ColumnName = "New Column",
	DeleteColumns = if List.IsEmpty(List.Select(List.Transform(Table.ColumnNames(Source), each Text.Contains(_, ColumnName)) ,each _ = true)) then Source else 
		Table.RemoveColumns(Source, List.Difference(Table.ColumnNames(Source), List.FirstN(Table.ColumnNames(Source), each not(Text.Contains(_,  ColumnName))) ))
in
	DeleteColumns

         

        Copy paste this code to the advanced editor in a new blank query to see how the solution works.

        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy