cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jct999
Helper III
Helper III

[PowerQuery] Function to add columns based on a list of column names

Hello everyone,

I would like to create a function that do the following :


Given 2 parameters :

- A table

- A list of name for new columns. Ex: {"COUNTRY", "CUSTOMER_NAME"}


Return : The input table with new columns. The added columns should be created if they do not already exist in the input table. Their types should be Text, and values should be null.


How can I do that ?
(My thought is that using List.Accumulate might be a good starting point... but can't see how to go further)

 

 

Thanks
Regards

1 ACCEPTED SOLUTION

Here is the solution I found. (May be helpfull for others)

 

let myFunc = ( myTable, myNewColumnList) =>

let foo = List.Accumulate(

myNewColumnList,
myTable,
(current_table, new_name) =>

if List.Contains(Table.ColumnNames( current_table), new_name)
then current_table
else Table.AddColumn(current_table, new_name, each null)

)

in foo

in myFunc

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

How many NULL values do you need in each row of the columns? Can you take the column and pivot it, to make the list into columns?

Izzii0x_0-1631614475118.png

I added a custom column which is just blank (null)

Izzii0x_3-1631614666892.png

 

Then hit "pivot column" whilst having the List highlighted:

Izzii0x_2-1631614599119.png

And then I get the following:

Izzii0x_4-1631614700631.png

 

 

It doesn't quite get you 'NULL' but gets the column names, and then adding new rows to that list will automatically add new columns.

 

 

I am confused, did you just copy my answer over or were you trying to reply to it?

Izzii0x
Frequent Visitor

How many NULL values do you need in each row of the columns? Can you take the column and pivot it, to make the list into columns?

Izzii0x_0-1631614475118.png

I added a custom column which is just blank (null)

Izzii0x_3-1631614666892.png

 

Then hit "pivot column" whilst having the List highlighted:

Izzii0x_2-1631614599119.png

And then I get the following:

Izzii0x_4-1631614700631.png

 

 

It doesn't quite get you 'NULL' but gets the column names, and then adding new rows to that list will automatically add new columns.

 

 

Here is the solution I found. (May be helpfull for others)

 

let myFunc = ( myTable, myNewColumnList) =>

let foo = List.Accumulate(

myNewColumnList,
myTable,
(current_table, new_name) =>

if List.Contains(Table.ColumnNames( current_table), new_name)
then current_table
else Table.AddColumn(current_table, new_name, each null)

)

in foo

in myFunc

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors