Dynamically replace headers from lookup table - is it doable?

[GreenBoy]

Hi


I have data provided form a data base that uses a multitude of formats for the field headers. This ranges from useful clear english headers e.g. "Target Price", through to almost meaningless jumbles of letters such as "DBdEx" and then the almost usesul "Billing_PO_Request_Date.2"


What I want to do is change the names of the colums to Clear titles that can be used as a part of my reports.


I can put together a table of all the existing field headers and the replacement value easily enough, but what I cannot seem to work out how to do is replace the headers in my table with the the new headers.


The table changes periodically with new fields etc so I dont want ot have to hard code the information in the query. Additionally it would be great if this could be applied to other queries.


Any help pointing me in the right directio would be gratefully recieved.


GreenBoy

 

[horseyride]

One way

There's probably a two-line method to doing this but one solution is (a) create replacement Table1 (b) extract titles from Table2 (c) merge to get the replacement names (d) put back together into Table2

let
    //Table1 is two column table with columns titles Old and New
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplacementTable = Table.TransformColumnTypes(Source,{{"Old", type text}, {"New", type text}}),
    //Table2 is any table where column titles will be replaced using Table1
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    DemoteHeaders = Table.DemoteHeaders(Source2),
    HeaderTransposed =Table.FromList(Table.ColumnNames(Source2)),
    #"Merged Queries" = Table.NestedJoin(HeaderTransposed,{"Column1"},ReplacementTable ,{"Old"},"Headers",JoinKind.LeftOuter),
    #"Expanded Headers" = Table.ExpandTableColumn(#"Merged Queries", "Headers", {"New"}, {"Headers.New"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Headers",{"Column1"}),
    HeaderTransposed2 = Table.Transpose(#"Removed Columns"),
    CombinedTables = Table.Combine({HeaderTransposed2,DemoteHeaders}),
    PromoteHeaders = Table.PromoteHeaders(CombinedTables),
    RemoveFirstRowFormerHeaders = Table.Skip(PromoteHeaders,1)
in
    RemoveFirstRowFormerHeaders

 

[horseyride]

Actually this works as well, much shorter, but previous method fails gracefully if there is a match, this one just dies

let
    //Table1 has two columns with text to replace in column 1 and replacement text in column 2
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Renamed = Table.RenameColumns(Source2,Table.ToRows(Source))
in
    Renamed

 

[GreenBoy]

Hey Horseyride - thanks for the repl, I dont think I would have gotten even close to this on my own - Ill give these a try when I'm back on my work PC and let you know how i get on.