Add Multiple Columns in One Step - Power Query

[Shreesurya]

Hi,

How to add Three Custom Column in Powerquery with static Data in one step.

I don't want to create a new step every time I add the column.

Below is an example of a table I want to add. Please help me with this.

A	B	C
1	2	3
1	2	3
1	2	3
1	2	3

 

[alansidman]

Re: Add multiple columns in a single step (Power Query)

Hi , To what I could understood you wnat to append columns if you append two columns that have different columns the ones that don't exist in the other table will automaticly be filled with nulls so you don't need to create those addtional columns: Regards, MFelix

community.powerbi.com

 

[Shreesurya]

I am not trying to append two columns, I want to create 3 separate columns manually and have a static number in each row of the column.

I am adding 3 columns every time in the query, I am trying to reduce the step by adding all 3 columns at once in a step.

 

[JGordon11]

let
   NumRows = 10,
   Source = Table.FromList(List.Generate(() => [x=1,A=1,B=2,C=3], each [x] <=NumRows,each [x=[x]+1,A=1,B=2,C=3],each [[A],[B],[C]]), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   ExpandRecords = Table.ExpandRecordColumn(Source,"Column1", {"A".."C"}, {"A".."C"})
in
   ExpandRecords

 

[the_original_invisible]

If you know the names of the columns you want to add and hold them in a list, you can easily add any number of extra columns. The below steps assume that you hold the column names that you want to add in a list called "myListName". Try something like this

#"Added Custom items from LIST" = Table.AddColumn(#"myPreviousStep", "Custom.1", each if Table.HasColumns(#"myPreviousStep",myListName) then #"myPreviousStep" else Table.Combine({#"myPreviousStep",Table.FromRows({},myListName)})),
#"Expanded LIST called myListName" = Table.ExpandTableColumn(#"Added Custom items from LIST", "Custom.1", myListName),
#"Replaced values in items from myListName" = Table.ReplaceValue(#"Expanded LIST called myListName",null,1,Replacer.ReplaceValue,myListName),
#"Removed Duplicates rows" = Table.Distinct(#"Replaced values in items from myListName", {"Client Name"}),

 

[the_original_invisible]

Or faster.....

If you know the names of the columns you want to add and hold them in a list, you can easily add any number of extra columns. The below steps assume that you hold the column names that you want to add in a list called "myListName". Try something like this:-

#"Added Custom items from LIST" = Table.AddColumn(#"myPreviousStep", "Custom", each if Table.HasColumns(#"myPreviousStep",myListName) then #"myPreviousStep" else Table.Combine({#"myPreviousStep",Table.FromRows({},myListName)})),
    #"Expand list items" = #"Added Custom items from LIST"{0}[Custom],
    #"Replaced values in items from myListName" = Table.ReplaceValue(#"Expand list items",null,1,Replacer.ReplaceValue,myListName),

 

[shaowu459]

= let a={"A","B","C"} in List.Accumulate(a,Source,(x,y)=>Table.AddColumn(x,y,each List.PositionOf(a,y)+1))

 

[shaowu459]

= let a={"A","B","C"} in #table(Table.ColumnNames(Source)&a,Table.ToList(Source,each _&List.Transform(a,(x)=>List.PositionOf(a,x)+1)))

 

[shaowu459]

let
    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    a={"A","B","C"}
in
    Table.FromRecords(
                       Table.TransformRows(
                                           Source,
                                           each _&List.Accumulate(a,[],(x,y)=>Record.AddField(x,y,List.PositionOf(a,y)+1))
                                          )
                     )

 

[shaowu459]

Many ways to add more than one columns in one step.

let
    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    a={"A","B","C"},
    res=Table.Group(
                    Source,
                    "Data",
                    List.Transform(
                                    a,
                                    each {_,(x)=>List.PositionOf(a,_)+1}
                                  )
                   )
in
    res