Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alex_monkey
Frequent Visitor

Automate the creation of columns

Dear all,

 

I am new to Power Bi, Power Queries and the M language. I can program in python a C++, but I am having hard time to get a sense of how to automate things in the M language. 

 

What I want to do is to make an operation on all columns if the column name matches any string in a list that I provide.

More precisely, I have a table which looks like this:

 

            A1_B1    A1_B2    A1_B3    A1_B4    A2_B1    A2_B2    A2_B3    A2_B4    A3_B1    A3_B2    A3_B3    A3_B4

row1    12          123        52          54          32          543        34          2354      53          23          3            54

row2    22          133        12          24          42          543        34          54          5            235        1            58

...

 

And I have a list of strings, say ["A2","A3"]. What I want is, for every column for which the name starts with an element of that list, to create a new column which is the sum of the values in the columns ending with "B3" and "B4". So in this case I want 2 new columns: SUM(A2_B3, A2_B4) and SUM(A3_B3, A3_B4).

I actually have about 2000 columns in my table and I will want to make this kind of operation on many elements. How can I automate this, such that I only have to provide the list of strings (["A2","A3"]) ?

 

Any help would be much appreciated, even if it is just a list of functions that I should/could call.

Thank you!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @alex_monkey,

 

If your table is already in the format you have and you have other table with the strings you want to check, why don't you implement DAX on it to make your calculations it will give you more flexibility and as @quentin_vigne said you don't have to grow your model.

 

Regards

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @alex_monkey,

 

If your table is already in the format you have and you have other table with the strings you want to check, why don't you implement DAX on it to make your calculations it will give you more flexibility and as @quentin_vigne said you don't have to grow your model.

 

Regards

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



quentin_vigne
Solution Sage
Solution Sage

Hi @alex_monkey

 

Are you familiar with SQL ? 

 

i'm very suprised by the number of columns, it is going to be very slow (the more columns it has, the more slow it is)

Can't you pivot your table so that columns are rows and rows are columns ? It will be easier to do what you want AND it will be better for performance 

 

I don't know how to add new column in an automatic way but if you want you can follow this tutorial on pivoting row/columns : 

http://radacad.com/pivot-and-unpivot-with-power-bi

 

And then you could use a simple IF() function for a newcolumn 

 

- Quentin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.