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
VeroNikka
New Member

A new column based on text content of 7 other columns.

Screenshot 2021-07-16 at 13.42.20.pngHi,

I hope you are well.

I would like to create a new column that if in columns 1-7 was more 'E' then it will be I and if there was more I then it will return I. How do I do that in PowerBI? Please help as I couldn't find any tutorials on this.

Thank you,

Best,

V.

 

1 ACCEPTED SOLUTION
fhill
Resident Rockstar
Resident Rockstar

 

There is probably a mroe programmatic way of doing this, but here's one option...

 

1.  Create a New Column merging all the other columns together....

MERGE = 'Table'[1] & 'Table'[2] & 'Table'[3] & 'Table'[4] & 'Table'[5] & 'Table'[6] & 'Table'[7]
 
These next few columns are easily merged together, i'm just keeping them seperat to help explain...
2.   Now count all the occurances of E
COUNT E = LEN('Table'[MERGE]) - LEN(SUBSTITUTE('Table'[MERGE],"E",""))
3. Now count all the occurances of I
COUNT I = LEN('Table'[MERGE]) - LEN(SUBSTITUTE('Table'[MERGE],"I",""))
4. You'll likely want to put better logic here in case there's NO E or I, but it's simple IF statements now to figure out your final result...
RESULT = IF('Table'[COUNT E] > 'Table'[COUNT I], "E", "I")
 
fhill_0-1626441939499.png

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




View solution in original post

2 REPLIES 2
fhill
Resident Rockstar
Resident Rockstar

 

There is probably a mroe programmatic way of doing this, but here's one option...

 

1.  Create a New Column merging all the other columns together....

MERGE = 'Table'[1] & 'Table'[2] & 'Table'[3] & 'Table'[4] & 'Table'[5] & 'Table'[6] & 'Table'[7]
 
These next few columns are easily merged together, i'm just keeping them seperat to help explain...
2.   Now count all the occurances of E
COUNT E = LEN('Table'[MERGE]) - LEN(SUBSTITUTE('Table'[MERGE],"E",""))
3. Now count all the occurances of I
COUNT I = LEN('Table'[MERGE]) - LEN(SUBSTITUTE('Table'[MERGE],"I",""))
4. You'll likely want to put better logic here in case there's NO E or I, but it's simple IF statements now to figure out your final result...
RESULT = IF('Table'[COUNT E] > 'Table'[COUNT I], "E", "I")
 
fhill_0-1626441939499.png

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Thank you very much for your help! 🙂

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.