cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twister8889
Helper V
Helper V

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

 

removeColumnsSpecificName.png

1 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

View solution in original post

9 REPLIES 9
Jimmy801
Community Champion
Community Champion

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

@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)

 

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

View solution in original post

I'm so sorry, my fault 

 

Thank you so much.

 

harshnathani
Super User
Super User

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)

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

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)

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

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.

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors