How to reorganise text data into two columns

[DianaT]

Hi all,

I am new to Power bi and I am at a complete loss

I have the following dataset. What I try to do is to eliminate blank and to force the two agent names on each row into Plan manager 1 and Plan manager 2. For example, PM1 should be Simone and PM2 should be Claire on the first row:

Agent 1                 Agent 2                    Agent 3                       Agent 4                    PM 1                     PM 2

Simone                 Claire

Simone                                                 Jane

                                                             Jane                              Peter

                             Claire                       Jane

Simone                                                                                       Peter

Any expert help will be greatly appreicated here. Thank you very much.

Diana

[v-alq-msft · Accepted solution]

Hi, @DianaT 

Based on your description, i created data to reproduce your scenario.

You can click ‘Edit Query’, go to Query Editor, select ‘Agent1’ and ‘Agent2’, choose ‘Transform’ pane, click ‘Merge Columns’.

In ‘Merge Columns’, the configuration is as follows.

You may repeat the above steps to merge the result with ‘Agent3’ and ‘Agent4’.

Then you can go to ‘Transform’ pane, click ‘Split Columns’, choose ‘By Lowercase to Uppercase’ and rename the correspond columns.

Result:

If I misunderstand the your thoughts, please show us your expected output. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DianaT]

Thank you so much! I was so stuck into thinking this had to be solved through DAX. This is great.

[JarroVGIT]

Alright, another fun one! The solution I've created was one with calculated columns. I started with this table:

Now first, we are going to concatenate all names in all columns and filter out the empty values. We create a new column with the following DAX and result:

Concatenated = 
VAR name1 = IF('Table'[Name1] = "", "abc1", 'Table'[Name1])
VAR name2 = IF('Table'[Name2] = "", "abc1", 'Table'[Name2])
VAR name3 = IF('Table'[Name3] = "", "abc1", 'Table'[Name3])
VAR name4 = IF('Table'[Name4] = "", "abc1", 'Table'[Name4])
VAR concatenadText = name1&"--"&name2&"--"&name3&"--"&name4
VAR firstFiltered = SUBSTITUTE(concatenadText, "--abc1", "")
VAR twoNames = SUBSTITUTE(firstFiltered, "abc1--", "")
RETURN
twoNames

This could've been cleaner but this way you can see every step what I am trying to do. Resulting in the following table:

Note i've used double dashes, as Names might contain single dashes

Now, the following dax created PM1 and PM2 column:

PM1 = 
VAR ind = FIND("--", 'Table'[Concatenated], 1)
RETURN
LEFT('Table'[Concatenated], ind-1)

PM2 = 
VAR ind = FIND("--", 'Table'[Concatenated], 1)
RETURN
RIGHT('Table'[Concatenated], LEN('Table'[Concatenated])-ind-1)

The end result looks like this:

Well, that's it! That was a fun question to answer 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[DianaT]

Thank you so much. Don't think I would ever come up with this. Speaking of steep learning curve. I'll test this on my data and see how this DAX works. Cheers!