Change granularity

[Anonymous]

Hello, I am working on a project to analyse certain customer opportunities and I want to analyse which employees are available per opportunity. Right now I have a table "new_candidates" with employee id's in (see sample data), the problem is that there are multiple employees in one row in the same column. So right now my granularity is one row per opportunity. I would like to change this to 1 row per employee opportunity/availability.

Sample data:

data right now:

start date	Topic	Candidates	Description	cloud	probability
01/05/2020	data lake	10000003, 10000006, 10000009	Description 1	1	100
24/04/2020	data integration	10000010, 100000000	Description 2	2	70
05/05/2020	Business Intelligence	10000008	Description 3	1	90

Desired data:

start date	Topic	Candidates	Description	cloud	probability
01/05/2020	data lake	10000003	Description 1	1	100
01/05/2020	data lake	10000006	Description 1	1	100
01/05/2020	data lake	10000009	Description 1	1	100
24/04/2020	data integration	10000010	Description 2	2	70
24/04/2020	data integration	100000000	Description 2	2	70
05/05/2020	Business Intelligence	10000008	Description 3	1	90

Any ideas?

[camargos88]

Hi @Anonymous ,

You can split this column like:

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[Anonymous]

Yes I know but that isn't what I am looking for. That way when I have a mapping table I still can't connect to that table. I need a row per employee for each opportunity

[camargos88]

Hi @Anonymous ,

Sorry, I didn't get what you need.

Can you give more details ?

Ricardo

[Anonymous]

Hi, @camargos88 sure. Right now I have data like this:

start date	Topic	Candidates	Description	cloud	probability
01/05/2020	data lake	10000003, 10000006, 10000009	Description 1	1	100
24/04/2020	data integration	10000010, 100000000	Description 2	2	70
05/05/2020	Business Intelligence	10000008	Description 3	1	90

Where I would like to go to would look something like:

start date	Topic	Candidates	Description	cloud	probability
01/05/2020	data lake	10000003	Description 1	1	100
01/05/2020	data lake	10000006	Description 1	1	100
01/05/2020	data lake	10000009	Description 1	1	100
24/04/2020	data integration	10000010	Description 2	2	70
24/04/2020	data integration	100000000	Description 2	2	70
05/05/2020	Business Intelligence	10000008	Description 3	1	90

Does this makes sense?

[mitsu]

Here is what might work . Split  the columns by delimiter . Select the columns that you created by split and unpivot it .   You should get the desired result 

[Anonymous]

Hi @mitsu, this indeed worked, but isn't future proof. What happen when in future I have 4 possible candidates? then I would have to transform data again and select the 4th column as well?

[mitsu · Accepted solution]

Good Point  this can be handled by creating a custom column , with text . split function .  Refer the following M code for your reference . 

 Reference : https://www.youtube.com/watch?v=9krfJLv8ENk

let
Source = Excel.Workbook(File.Contents("...Documents\Smap.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"tart date", type date}, {"Topic", type text}, {"Candidates", type text}, {"Description", type text}, {"cloud", Int64.Type}, {"probability", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Split Candidates", each Text.Split([Candidates],",")),
#"Expanded Split Candidates" = Table.ExpandListColumn(#"Added Custom", "Split Candidates")
in
#"Expanded Split Candidates"

[Anonymous]

Cool, thanks alot!