Remove all columns since one specific column

[twister8889]

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

[Jimmy801]

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]

@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 · Accepted solution]

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

[twister8889]

I'm so sorry, my fault 

Thank you so much.

[harshnathani]

Hi @twister8889 ,

You cna do this in Power Query.

https://community.powerbi.com/t5/Desktop/how-do-you-remove-columns-in-query-editor/td-p/266965

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[twister8889]

Hi @harshnathani, thank you for your answer...

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

[harshnathani]

Hi @twister8889 ,

You can see these videos.

https://www.youtube.com/watch?v=PZKlcgCZX6w

https://www.youtube.com/watch?v=xamU5QLNiew

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[twister8889]

I saw the video, but my question is: How can I remove all columns after New Columns?
I need to remove dynamically the columns: New Columns | State | Columns1| Colum2|New Columns| State| Column1| Column2

[ImkeF]

Hi @twister8889 ,

check the column you want to keep -> rightclick mouse -> remove other columns.

That creates a Table.SelectColumns command that explictely determines the columns to keep.