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

[VeroNikka]

Hi,

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.

[fhill · Accepted solution]

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")

[VeroNikka]

Thank you very much for your help! 🙂