Delimitate Column into Single Column

[rshifflet]

This may be a very simple thing, so please excuse my ignorance. I will try and articulate this the best that I can. When I go to edit my quiries, I have several columns with values that are comma delimited. However, when I apply these columns to a chart, or graph, it treats each combined text as a unique value. 

 

I believe that I need the be able to have the data line up like below, in order to get the chart's to break apart by the unique values. 

I know that I can split each column by the coma delimeter into multiple columns, however that could be up to 30 columns. When I try and add each column into the values to plot, I can get a graph but I can't get it to slice all the other surounding graphs. 

Is there a simple way to break the values apart at the coma and have them stack in the same column rather than splitting into multiple colums? That way all I have to do is "Fill Down" by the unique identifier and everyting slices just fine. I hope I'm making sence. 

Aaron 

[Anonymous · Accepted solution]

Hi,

According  to your description, you want to spilt the “LSR’s “ column by “,” and merge them to one column, right?

If as I said, you can follow below steps:

Source table:

 

1. Open the edit query and duplicate the base table:

 

2. Right-Click on LSR’s and choose the “spilt column” -> “By Delimiter”:

 

 

3.All select the columns and choose the “Unpivot Columns”:
 

 

4.Modify the formula of "Unpivot Columns":

Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value")[Value]

 

5. Close and apply the change, then create a visual.

 

Regards,
Xiaoxin Sheng

[rshifflet]

Xiaoxin_Sheng,

Thank you so much for your solution. It is exactly what I was looking for. 

[Greg_Deckler]

Split into two columns on comma. Make a hierarchy, Column1 at the top, Column2 below it and use that in your visual. This will activate drill down capabilities. Alternatively, you can use two treemaps, one for Column1 and one for Column2. Clicking on the Column1 treemap will automagically filter the other treemap.