Power Query - Add multiple blank columns

[borkybork]

Hi! I’m trying to add blank columns to my table, but when I do it creates a query with hundreds of repeated rows of the same data. So for example if I have 5 rows of data, it then creates the query and repeats that same data set a hundred times. Is there a better way of adding blank columns without this happening? I’m still learning power query so I’m not sure what I’m doing wrong. Below is the code I have. Thank you in advance!

=Table.AddColumn(# “Changed Type”, “ColumnName1”, each null)
& Table.AddColumn(# “Changed Type”, “ColumnName2”, each null)
& Table.AddColumn(# “Changed Type”, “ColumnName3”, each null)

…etc.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Cols = {"DOB", "Address", "City", "State", "Zip"},
    AddEmptyColumns = List.Accumulate(Cols, Source, (s,c)=> Table.AddColumn(s, c, each null))
in
    AddEmptyColumns

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Table2			Query Output
2	Name	Value		Name	Value	DOB	Address	City	State	Zip
3	A	8		A	8
4	B	5		B	5
5	C	9		C	9
6	D	3		D	3
7	E	2		E	2
8	F	4		F	4
9	G	1		G	1
10	H	7		H	7
11	I	6		I	6
12	J	10		J	10
13
Sheet2

 

[borkybork]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Cols = {"DOB", "Address", "City", "State", "Zip"},
    AddEmptyColumns = List.Accumulate(Cols, Source, (s,c)=> Table.AddColumn(s, c, each null))
in
    AddEmptyColumns

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Table2			Query Output
2	Name	Value		Name	Value	DOB	Address	City	State	Zip
3	A	8		A	8
4	B	5		B	5
5	C	9		C	9
6	D	3		D	3
7	E	2		E	2
8	F	4		F	4
9	G	1		G	1
10	H	7		H	7
11	I	6		I	6
12	J	10		J	10
13
Sheet2

Thank you!! Would you mind explaining what the 2nd part of the formula is doing?

AddEmptyColumns = List.Accumulate(Cols, Source, (s,c)=> Table.AddColumn(s, c, each null))

 

[JGordon11]

List.Accumulate goes through each item in a list, in this case the list named Cols.

It takes an initial value in this case the table named Source.

Then it applies a function to the current state represented by s in the function (s,c)=>. The current state is initially the initial value and can change with each iteration as it steps through the list.

So first step it takes the table Source and adds a column named c="DOB" filling each row with null. Then it takes that table and adds a column "Address", etc ... until the last item in the list.

It is a very useful function to allow looping in M code.

 

[borkybork]

List.Accumulate goes through each item in a list, in this case the list named Cols.

It takes an initial value in this case the table named Source.

Then it applies a function to the current state represented by s in the function (s,c)=>. The current state is initially the initial value and can change with each iteration as it steps through the list.

So first step it takes the table Source and adds a column named c="DOB" filling each row with null. Then it takes that table and adds a column "Address", etc ... until the last item in the list.

It is a very useful function to allow looping in M code.

Great, thanks so much! This is very helpful.