Forum Discussion
Change granularity
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?
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"
8 Replies
Hi Anonymous ,
You can split this column like:
If you consider it as a solution, please mark as a solution and kudos.
Ricardo
Hi Anonymous ,
Sorry, I didn't get what you need.
Can you give more details ?
Ricardo