Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a calculated column called 'Tasks' in 'Table1' that is delimited by commas which I would like to split into separate rows in a new table called 'Table2'. As 'Tasks' is a calculated column I am not able to use split column in Power Query, in any case I would like a separate table.
Any help most appreciated. Many thanks
Table1
Id Event Names Tasks
1 Halloween Bob, Kate invitations, emails, catering
2 Christmas Kate, Pete, Tom invitations, catering, decorations, carols,
Table2
Id Event Names Tasks
1 Halloween Bob, Kate invitations
1 Halloween Bob, Kate emails
1 Halloween Bob, Kate catering
2 Christmas Kate, Pete, Tom invitations
2 Christmas Kate, Pete, Tom catering
2 Christmas Kate, Pete, Tom decorations
2 Christmas Kate, Pete, Tom carols
Solved! Go to Solution.
@witbi Try this, also PBI file is attached below my signature:
Table =
VAR WordCount =
ADDCOLUMNS (
witbi,
"@Word Count",
LEN ( witbi[Tasks] ) - LEN ( SUBSTITUTE ( witbi[Tasks], ",", "" ) ) + 1
)
VAR MaxWordCount =
MAXX ( WordCount, [@Word Count] )
VAR TempTable =
FILTER (
GENERATE ( WordCount, GENERATESERIES ( 1, MaxWordCount, 1 ) ),
[Value] <= [@Word Count]
)
VAR SplitTextByNumber =
ADDCOLUMNS (
TempTable,
"Final String", TRIM ( PATHITEM ( SUBSTITUTE ( witbi[Tasks], ",", "|" ), [Value] ) )
)
VAR Result =
SELECTCOLUMNS (
SplitTextByNumber,
"ID", [ID],
"Event", [Event],
"Tasks", [Final String]
)
RETURN
Result
@witbi Try this, also PBI file is attached below my signature:
Table =
VAR WordCount =
ADDCOLUMNS (
witbi,
"@Word Count",
LEN ( witbi[Tasks] ) - LEN ( SUBSTITUTE ( witbi[Tasks], ",", "" ) ) + 1
)
VAR MaxWordCount =
MAXX ( WordCount, [@Word Count] )
VAR TempTable =
FILTER (
GENERATE ( WordCount, GENERATESERIES ( 1, MaxWordCount, 1 ) ),
[Value] <= [@Word Count]
)
VAR SplitTextByNumber =
ADDCOLUMNS (
TempTable,
"Final String", TRIM ( PATHITEM ( SUBSTITUTE ( witbi[Tasks], ",", "|" ), [Value] ) )
)
VAR Result =
SELECTCOLUMNS (
SplitTextByNumber,
"ID", [ID],
"Event", [Event],
"Tasks", [Final String]
)
RETURN
Result
Many thanks Antriksh. Appreciate the quick reply and sample. This does what I was looking for!
You can use Split comma by delimeter to split the values into separate rows.
Click Edit queries to open Power Query Editor, select the column you want to split and click Split Column -> By Delimeter: Make sure the delimiter is correct, and Rows is selected in Split into:
Thanks kumar27
I am familiar with the Power Query split column function, however as the table column is calculated this doesn't seem to be a solution I can use. Any other thoughts?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |