Add Columns Dynamically

yevhen

New Member
Joined
Jan 5, 2025
Messages
8
Office Version
  1. 2021
Platform
  1. Windows
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="split_column"]}[Content],
    Splitted = Table.TransformColumnTypes(Table.SplitColumn( Source, "B", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"1","2","3"}),{{"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}})
in
    Splitted

Screenshot 2025-01-07 173644.png


Hi everyone. How to dynamically add columns with headers named like a column number when a new value after a comma in a column "B" is added?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Is this what you are trying to accomplish?

Power Query:
let
   
let
    Source = Excel.CurrentWorkbook(){[Name="split_column"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"A"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Temp", each List.Count(Text.Split([Value],","))),
    #"Calculated Maximum" = List.Max(#"Added Custom"[Temp]),
    ColumnVals = List.Transform({1..#"Calculated Maximum"},each "Value." & Number.ToText(_)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Added Custom", "Temp1", each List.Count(Text.Split([Value],",")))[Temp])},each "Value." & Number.ToText(_))),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Temp"}),
    ColumnNames = Table.ColumnNames(Table.RemoveColumns(#"Removed Columns",{"Attribute"})),
    ColumnNamesToTable = Table.FromList(ColumnNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Duplicated Column" = Table.DuplicateColumn(ColumnNamesToTable, "Column1", "Column1 - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","Value.","",Replacer.ReplaceText,{"Column1 - Copy"}),
    CreateList = Table.ToColumns(Table.Transpose(#"Replaced Value")),
    MainTable = #"Removed Columns",
    #"Renamed Columns" = Table.RenameColumns(MainTable,CreateList)
in
    #"Renamed Columns"
 
Last edited:
Upvote 0
Power Query:
let
A = Excel.CurrentWorkbook(){[Name="split_column"]}[Content],
B = Table.Combine(List.Transform(Table.ToRows(A), each let x = Text.Split(_{1},",") 
    in Table.FromRows({{_{0}} & x}, {Table.ColumnNames(A){0}} & List.Transform({1..List.Count(x)}, Text.From))))
in B

Regards,
 
Upvote 0
Sorry meant to update it with this code:

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="split_column"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"A"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Unpivoted Other Columns", "Temp", each List.Count(Text.Split([Value],",")))[Temp])},each "" & Number.ToText(_)))
in
    #"Split Column by Delimiter"
 
Upvote 0
Solution

Forum statistics

Threads
1,226,452
Messages
6,191,132
Members
453,641
Latest member
enfkkviesm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top