column management

[Anonymous]

I am definitely a PQ newbie.  Got an ugly dataset with 70+ rows.  A Spend, A Rotation, A Creative,  B Spend, B Rotation, B Creative... B2 Spend, B2 Creative, B2 Rotation, etc....  have no idea how this data was ever read.  It came to me unreconciled but PQ magic it is reconciled.  

 Have been able to determine pattern, textjoin related columns, remove other columns, unpivot and split by delimiter to get data.     This is a monthly report and data is from external source.  I want to fully automate this and feel like it needs some data validation to avoid breaking on refresh.

Two issues... I want to retain the file name and the date modifield from workbook as this data will stack and provide rolling 90 day summaries.    Got the data from content.    When promoting headers, I either want to promote every header but those two or rename them by position... I need to make them dynamic as the promoted values changes each time.   Table.RenameColumns{1}...  forgive my ignorance but I don't know if I can rename the two columns in same function.  Table.RenameColumns{1}.= Filename, {2}=Date_Modified, etc...

The bigger issue is column headers and indexing... my thought is to unpivot, capture all column names, index, load as table.    On Refresh I want to test the new column headers against this table and if they match proceed, if not, user makes necessary changes.     I am more familiar with Access queries and sequencing...  Feel like once I get the nuances, PQ and Power Bi will be my tool of choice

Any suggestions appreciated.

Thanks

T

[Stachu]

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
    MergedAsTable

other 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

[Anonymous · Accepted solution]

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