Rename Dynamic Number of Columns

Power Query
1

Hello,

Background:
I have some funky data that splits variables into additional columns. The number of additional columns the variables are split into is dynamic. In my last post (Merge Dynamic Number of Columns ), Melissa provided an awesome solution to dynamically merge these columns.

My issue now is I need to append multiple exports of this funky nature before using the solution Melissa provided.

I figured If I could rename the additional columns with a specific format in both files I could then simply append the two files. From there I could tweak Melissa’s solution to merge all the relevant columns.

Thus my ask:
Is it possible to rename columns based on a neighboring column? Additionally, If I don’t know how many neighboring columns there will be?

I would also need the new names to iterate so I could line them up between the different files.

example :     
                Input:  Description    Column1    Column2  
                Output: Description    Description_2    Description_3

Here are images of what I have and what I am hoping the outcome could be:

Mock Data:

Renaming_Mock_1
Renaming_Mock_11820Ă—303 29.1 KB

Desired Outcome:

Renaming_Mock_Outcome
Renaming_Mock_Outcome1818Ă—310 30.8 KB

COE Help - Dynamic Column Renaming.pbix (32.3 KB) Mock RAW QMS for COE.xlsx (10.8 KB)

2

Hi @Aaron.Im,

Give this a go.

let
    ListHeaders = Table.ColumnNames( #"Changed Type" ),
    ConvertToTable = Table.FromList( ListHeaders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    AddIndex = Table.AddIndexColumn( Table.FillDown( Table.AddColumn(ConvertToTable, "GroupColumn", each if Text.StartsWith([Column1], "Column") then null else [Column1]), {"GroupColumn"} ), "Index", 0, 1, Int64.Type), 
    HeaderTable = Table.Buffer( Table.AddColumn( Table.AddColumn( AddIndex, "Count", each List.Count( Table.SelectRows( AddIndex, (IT)=> IT[GroupColumn] = [GroupColumn] and IT[Index] <= [Index])[GroupColumn] ))[[GroupColumn], [Column1], [Count]], "NewColName", each if [Count] =1 then [GroupColumn] else [GroupColumn] & "_" & Text.From([Count]), type text)),
    
    Source = Excel.Workbook(File.Contents(FileLocation), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers2" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers2",{{"Date", type date}, {"Name", type text}, {"Description", type text}, {"Column4", type text}, {"Status", type logical}, {"Comment", type text}, {"Column7", type text}, {"Column8", type text}, {"Sub-Status", type text}}),
    RenameColumns = Table.RenameColumns( #"Changed Type", List.Zip( {HeaderTable[Column1], HeaderTable[NewColName]}))
in
    RenameColumns

.
It generates a HeaderTable that incl new col names

image

And uses List.Zip to rename the columns

image
image1497Ă—287 21.7 KB

I hope this is helpful

3 Likes
3

Hi @Aaron.Im, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Using the List.Generate function …

let
Origine = Excel.Workbook(File.Contents("C:\xxxxx\BI\Mock RAW QMS for COE.xlsx"), null, true),
Sheet1_Sheet = Origine{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Intestazioni alzate di livello" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

count=List.Count(Table.ColumnNames( #"Intestazioni alzate di livello" )),
names=Table.ColumnNames( #"Intestazioni alzate di livello" ),

newNames = List.Generate(
                    ()=>[newname=names{0},cidx=2, idx=1],
                    each [idx]<=count,
                    each [newname= if Text.Contains(names{[idx]}, "Col") then names{[idx]-[cidx]+1}&"-"&Text.From([cidx]) else names{[idx]}, 
                          cidx=if Text.Contains(names{[idx]}, "Col") then [cidx]+1 else 2,idx=[idx]+1],
                    each      [newname]

),
trc=Table.RenameColumns(#"Intestazioni alzate di livello",List.Zip({names,newNames}))

in
trc

2 Likes
5

Hi @Aaron.Im, we’ve noticed that no response has been received from you since the 20th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hi @Aaron.Im, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!