Forum Discussion
Power Query: Duplicate rows with multiple names
Hello,
Is it somehow possible to do the following in Power Bi Query:
When I have a project, with different people, can I extract it to multiple lines?
So for example:
| Names | Project | Date | Hours |
| David, Richard | Project X | 14-jul | 16 |
When I work with these names on an x-axis I will see 'David, Richard' as one name, but I want one line
of 8 hours for David and one line of 8 hours for Richard.
Names are always comma separated.
Is it possible to make two rows from this when I load the Excel file in Power BI?
Hi IAM ,
See below outcome:
Simple 3 transformation steps:
1. Add a custom column and add formula below to split the name by ", " and return as a list:
Text.Split([Names],", ")
2. Expand the list to new rows
3. Add a custom column to split the hour equally with formula below:
[Hours]/List.Count(Text.Split([Names],", "))
Regards
KT
3 Replies
Hi IAM ,
See below outcome:
Simple 3 transformation steps:
1. Add a custom column and add formula below to split the name by ", " and return as a list:
Text.Split([Names],", ")
2. Expand the list to new rows
3. Add a custom column to split the hour equally with formula below:
[Hours]/List.Count(Text.Split([Names],", "))
Regards
KT
- Right-click on the column
- Split column => by delimiter
- Select "comma", "advanced", "rows"
Woww thanks!!!
