Help needed on Complex Scenario

[kxj54590]

Hello Community Group,

I have recently came across a scenario where in the input file given link below has a table visual with columns Member & list of details. I wanted to achieve, the list of details field to be splitted across each column because the list of details field in the input file is grouped & concatenate for a member where i wanted to break it down into different columns as shown in the output below. How can we resolve this problem ? For refernce, i have attached the desired output of the screenshot as well. 

Input: 

https://ucmo0-my.sharepoint.com/:u:/g/personal/kxj54590_ucmo_edu/EfAziaQEZRVGpiUvWZERA2QBrAjnadx1J12...

@parry2k,  @Amin  , @Greg @amitchandak @pade @Paddy 

Output:

Please do the needful.

Thanks

Jay

[MFelix · Accepted solution]

Hi @kxj54590 ,

You can do this on the query editor and this will work has a single step using the split columns by delimiter:

Or you can do it using DAX to do columns you need to add the following syntax:

Column:

Test Column 1 = PATHITEM(SUBSTITUTE(Sheet1[List of details], ", ", "|"), 1)

Test Column 2 = PATHITEM(SUBSTITUTE(Sheet1[List of details], ", ", "|"), 2)

Test Column 3 = PATHITEM(SUBSTITUTE(Sheet1[List of details], ", ", "|"), 3)

Test Column 4 = PATHITEM(SUBSTITUTE(Sheet1[List of details], ", ", "|"), 4)

Measures:

Test Measure 1 = PATHITEM(SUBSTITUTE(SELECTEDVALUE(Sheet1[List of details]), ", ", "|"), 1)

Test Measure 2 = PATHITEM(SUBSTITUTE(SELECTEDVALUE(Sheet1[List of details]), ", ", "|"), 2)

Test Measure 3 = PATHITEM(SUBSTITUTE(SELECTEDVALUE(Sheet1[List of details]), ", ", "|"), 3)

Test Measure 4 = PATHITEM(SUBSTITUTE(SELECTEDVALUE(Sheet1[List of details]), ", ", "|"), 4)

Result in attach PBIX.

[kxj54590]

@MFelixThis would definitely work, but in case of import option, my apologize didnt mention the method of connection. However, in my case it is direct query mode (SSAS model). How this scenario can be achieved using this type of connection ?

[MFelix]

You need to use the measures for achieving the result. 

[kxj54590]

@MFelix With the above given measures? Sure, will give it a try and let you know. Thank you for taking time. 

[MFelix]

Correct