Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help needed on Complex Scenario

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/EfAziaQEZRVGpiUvWZERA2QBrAjnadx1J12k8yzAJnqPjw?e=h1z3bX

 

parry2k,  Amin  , Greg amitchandak pade Paddy 

 

Output:

 

 

Please do the needful.

 

Thanks

Jay

 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    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.

     

     

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        MFelix
        Super User
        5 years ago

        You need to use the measures for achieving the result.