none
Find and Replace a Header Name Dynamically RRS feed

  • Question

  • Hello,

    Is there a way to create a query where it can find and replace a header name that ends with a number with some other text?

    Example:

    I need to find "US57210031" (which changes based on data source) and want to replace it with "Result".  However, my data source column header name changes as well as the column location, so it would need to be dynamic.  Ideally there would be a function that does this without there being any hard coded information in the syntax.

    I was hoping for something like...     NewHeader = Table.Headers. Replace(Source, EndsWith"{0..9}, "Result"}")

    Thanks for any help on this,

    Regards
    Darin

    • Moved by Chenchen Li Tuesday, December 5, 2017 3:10 AM PowerQuery
    Monday, December 4, 2017 11:49 PM

Answers

  • If you prefer to work with the promoted headers, you can do something like the following:

    let ......
    PromotedHeaders = ...,
    ColumnNames = Table.ColumnNames(PromotedHeaders),
    AlphaNumericName = List.Select(ColumnNames, each List.Contains({"0".."9"}, Text.End(_, 1))){0},
    RenamedColumn = Table.RenameColumns(PromotedHeaders, {AlphaNumericName, "Result"})
    in
    RenamedColumn



    • Edited by Colin Banfield Tuesday, December 5, 2017 3:10 PM
    • Marked as answer by LearningPQ Saturday, December 9, 2017 6:52 AM
    Tuesday, December 5, 2017 3:07 PM

All replies

  • Hello Darin,

    According to your description and your previous thread, you are using Power Query. Excel does not provide any formula like Table.PromoteHeaders as your picture. This forum(Excel for Developers) is for development issues when using Excel Object Model. Your issue about Power Query is out of scope. I would move this thread into Power Query forum. Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 5, 2017 3:09 AM
  • You could create a custom function that would do this. I'd probably start by:

    1. Demoting the Headers
    2. Navigating to the very first record (Which are the headers)
    3. do a dynamic replacing of all the numbers with blank spaces
    4. Convert that to a list and use Liz.Zip with the original column header names

    here's a sample on how you can accomplish that (minus the replacing values part).

    let
        Origen = {[A=2,B=4]},
        #"Convertida en tabla" = Table.FromList(Origen, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Se expandió Column1" = Table.ExpandRecordColumn(#"Convertida en tabla", "Column1", {"A", "B"}, {"A", "B"}),
        #"Encabezados con nivel disminuido" = Table.DemoteHeaders(#"Se expandió Column1"),
        #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados con nivel disminuido",{{"Column1", type any}, {"Column2", type any}}),
        Personalizado1 = List.Zip( {Table.ColumnNames(#"Encabezados con nivel disminuido"), Record.ToList( #"Tipo cambiado"{0}) }),
        Personalizado2 = #"Encabezados con nivel disminuido",
        #"Columnas con nombre cambiado" = Table.RenameColumns(Personalizado2, Personalizado1)
    in
        #"Columnas con nombre cambiado"
    since you want to get rid of every single number inside that text string, it would be way better to create a recursive function that would accomplish that. 

    Tuesday, December 5, 2017 6:51 AM
  • If you prefer to work with the promoted headers, you can do something like the following:

    let ......
    PromotedHeaders = ...,
    ColumnNames = Table.ColumnNames(PromotedHeaders),
    AlphaNumericName = List.Select(ColumnNames, each List.Contains({"0".."9"}, Text.End(_, 1))){0},
    RenamedColumn = Table.RenameColumns(PromotedHeaders, {AlphaNumericName, "Result"})
    in
    RenamedColumn



    • Edited by Colin Banfield Tuesday, December 5, 2017 3:10 PM
    • Marked as answer by LearningPQ Saturday, December 9, 2017 6:52 AM
    Tuesday, December 5, 2017 3:07 PM
  • Hello, Thanks for the info Celeste, I'm new at this so I appreciate you letting me know.
    Tuesday, December 5, 2017 7:22 PM
  • I've been wondering for some time how you could create and call on variables, but I've always found other ways to accomplish what I was looking for. Quite simply it appears. This was great to see - thanks for sharing.

    Ian

    Monday, December 11, 2017 2:30 PM