Forum Discussion
Split Text into rows using DAX
Hi power BI expert, I need to split into rows using DAX instead of using Power Query due to performance (the data is too large). For info, previously i was using Power Query but I'm keep getting ...
Hi,
Thank you for your message.
Please check the below and the attached file whether it suits your requirement.
Table V2 = VAR _gt = GENERATESERIES ( 1, 100, 1 ) VAR _t = ADDCOLUMNS ( Data, "@separator", SUBSTITUTE ( Data[List of Students], ",", "|" ) ) VAR _generatetable = GENERATE ( _gt, ADDCOLUMNS ( _t, "@Name", PATHITEM ( [@separator], [Value] ) ) ) RETURN FILTER ( SELECTCOLUMNS ( _generatetable, "@ClassName", Data[Class Name], "@StudentName", [@Name] ), [@StudentName] <> BLANK () )
Thank you so much for this Jihwan_Kim . This is what I wanted. But I have question, based on below syntax i understand that it is based on number of text but what if we dont know how long will be the values? for example in future it might have more than 4? should i just assume it will go up to 20 then I have to set it to var _20 to ensure it will pickup and all values will be splitted accordingly?
VAR _one =
ADDCOLUMNS ( _t, "@1", PATHITEM ( [@separator], 1 ) )
VAR _two =
ADDCOLUMNS ( _t, "@2", PATHITEM ( [@separator], 2 ) )
VAR _three =
ADDCOLUMNS ( _t, "@3", PATHITEM ( [@separator], 3 ) )
VAR _four =
ADDCOLUMNS ( _t, "@4", PATHITEM ( [@separator], 4 ) )
Hi,
Thank you for your message.
Please check the below and the attached file whether it suits your requirement.
Table V2 =
VAR _gt =
GENERATESERIES ( 1, 100, 1 )
VAR _t =
ADDCOLUMNS (
Data,
"@separator", SUBSTITUTE ( Data[List of Students], ",", "|" )
)
VAR _generatetable =
GENERATE ( _gt, ADDCOLUMNS ( _t, "@Name", PATHITEM ( [@separator], [Value] ) ) )
RETURN
FILTER (
SELECTCOLUMNS (
_generatetable,
"@ClassName", Data[Class Name],
"@StudentName", [@Name]
),
[@StudentName] <> BLANK ()
)Thank you so much for your help 😀