Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |