Forum Discussion
Delete all columns between two columns
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 |
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.
3 Replies
kleetus51 , select all the columns you want and remove others, if that can help
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.
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.
