Add Columns Dynamically

[yevhen]

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

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?

 

[ExcelToDAX]

Is this what you are trying to accomplish?

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"

 

[alex78]

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,

 

[ExcelToDAX]

Sorry meant to update it with this code:

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"