Forum Discussion
column management
Thanks Stachu. I appreciate the function showing the way to loop thru columns to get renamed headers. Renaming columns is common need here so that will definitely be useful. The merge table is something I never considered.
This month's version has to be distributed today so I am going with the merged table option this round. Beyond just merging the table to get all column headers, I wasn't sure how to get headers back in play. The join gets my column headers back to full dataset in proper order. I need to tweak solution just a bit to add conditional column that tests header name and order between Defined_Cols and Cols_InCurrFile. I realize the join could either allow all cols to show up or just those from defined list but my next step is joining fields together. Need to see what is off and decide whether new info needs to be included, excluded or reordered. Defined_Cols will be static but editable table for user. Then concatenate based on data pattern. There are 6 columns in each column grouping, 4 cols are sequential plus column 63 and last column, except last group which is all sequential.
Thank you for two awesome solutions.
T
you could do something like this (paste the code as a new blank query)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssvSfRITUxJLVLSUYIwDOEsI6VYnWglIyDfCYgjwTxjIMsZiKOUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
Promoted_Select = Table.RemoveColumns(Source,{"Column1"}),
Promoted_Promote = Table.PromoteHeaders(Promoted_Select, [PromoteAllScalars=true]),
NotPromoted_Select = Table.SelectColumns(Source,{"Column1"}),
NotPromoted_Rename = Table.RenameColumns(NotPromoted_Select,{{"Column1", "NewHeader"}}),
NotPromoted_RemoveFirstRow = Table.Skip(NotPromoted_Rename,1),
MergedAsList = Table.ToColumns(NotPromoted_RemoveFirstRow) & Table.ToColumns(Promoted_Promote),
MergedAsTable = Table.FromColumns(MergedAsList, Table.ColumnNames(NotPromoted_RemoveFirstRow) & Table.ColumnNames(Promoted_Promote))
in
MergedAsTableother option is to add an index column, separate the tables, rename them and then join together based on the index
you can get the names of the columns dynamically by Table.ColumnNames
Thanks Stachu. I appreciate the function showing the way to loop thru columns to get renamed headers. Renaming columns is common need here so that will definitely be useful. The merge table is something I never considered.
This month's version has to be distributed today so I am going with the merged table option this round. Beyond just merging the table to get all column headers, I wasn't sure how to get headers back in play. The join gets my column headers back to full dataset in proper order. I need to tweak solution just a bit to add conditional column that tests header name and order between Defined_Cols and Cols_InCurrFile. I realize the join could either allow all cols to show up or just those from defined list but my next step is joining fields together. Need to see what is off and decide whether new info needs to be included, excluded or reordered. Defined_Cols will be static but editable table for user. Then concatenate based on data pattern. There are 6 columns in each column grouping, 4 cols are sequential plus column 63 and last column, except last group which is all sequential.
Thank you for two awesome solutions.
T