Delete all columns between two columns

[kleetus51]

I have a dataset that fluctuates in the number of columns. I need to do some transformations where I delete all the columns between the columns "Key" and "Config" columns, including the Config column. The number of columns between will fluctuate in name and number and there will be a variable number of columns after "Config". Here are some examples. Any ideas?

This:

Key	Order #	Reference	Config	Thickness	Width	Length
1	1234	xyz	23068	2	30	68
2	4567	abc	1.52870	1.5	28	70
3	8910	def	22468	2	24	68
4	2345	ghi	1.3753068	1.375	30	68

Would change to this:

Key	Thickness	Width	Length
1	2	30	68
2	1.5	28	70
3	2	24	68
4	1.375	30	68

This:

Key	Order #	Config	Thickness	Width	Length	Transfer
1	1234	23068	2	30	68	TRUE
2	4567	1.52870	1.5	28	70	FALSE
3	8910	22468	2	24	68	FALSE
4	2345	1.3753068	1.375	30	68	TRUE

Would change to this:

Key	Thickness	Width	Length	Transfer
1	2	30	68	TRUE
2	1.5	28	70	FALSE
3	2	24	68	FALSE
4	1.375	30	68	TRUE

[v-yalanwu-msft · Accepted solution]

Hi, @kleetus51 ;

You could compete it in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TU5JEoAwCPtLzx2nBbr4lk4P7voD9fUCOuoBkgwkUIrxxhoPSAz7cXIHdDELcqHjxqraoppCTAxdP4irCZCTu5kYxMValpFpbr0Mx2mWIdCbCvSlkl4k8S/rplmYwvOC8v8b9QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, #"Order #" = _t, Reference = _t, Config = _t, Thickness = _t, Width = _t, Length = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Order #", Int64.Type}, {"Reference", type text}, {"Config", type number}, {"Thickness", type number}, {"Width", Int64.Type}, {"Length", Int64.Type}}),
a = Table.ColumnNames(#"Changed Type"),
start=List.PositionOf(a,"Key"),
end=List.PositionOf(a,"Config"),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
T1=Table.ToColumns( #"Demoted Headers"),
select1=List.Range(T1, start,1),
select2=List.Range(T1, end),
result=Table.FromColumns(List.Union({select1,select2})),
#"Promoted Headers" = Table.PromoteHeaders(result, [PromoteAllScalars=true])
in
    #"Promoted Headers"

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@kleetus51 , select all the columns you want and remove others, if that can help

[kleetus51]

The number and names of the columns that need to be deleted will vary. I need a solution that says "delete all columns between "Key" and "Config", regardless of their name and number of columns between them.