Splitting and pivoting comma separated column

[kboscar1]

Hi all,

After playing around with both Power Query and Dax functions, I can officially say that I'm stuck. 

I have a column in my table that looks like this:

Project Number	Complexity Counts
1	Simple (2), Medium (1)
2	Medium (5)
3	Large (1), Simple (1)
4	Mini (.5)
5	Mini (1), Medium (3)

I want to both split by delimeter and pivot the columns so that my table looks like this:

Project Number	Simple	Medium	Large	Mini
1	2	1	0	0
2	0	5	0	0
3	1	0	1	0
4	0	0	0	.5
5	0	3	0	1

Here is what I have tried:

1. Power Query -

• Split column by the comma delimeter 

  Project	Complexity Counts 1.1	Complexity Counts 1.2
  1	Simple (2)	Medium (1)
  2	Medium (5)	null
  3	Large (1)	Simple (1)
  4	Mini (.5)	null
  5	Mini (1)	Medium (3)

• Split each of these new columns by the "(" and then remove the extra ")"

  Project	Complexity Counts 1.1	Complexity Counts 1.1.1	Complexity Counts 1.2	Complexity Counts 1.2.1
  1	Simple	2	Medium	1
  2	Medium	5	null	null
  3	Large	1	Simple	1
  4	Mini	.5	null	null
  5	Mini	1	Medium	3

• Pivot "Complexity Counts 1.1" into Complexity Counts 1.1.1

• Project	Simple	Medium	Large	Mini	Complexity Counts 1.2	Complexity Counts 1.2.1
  1	2	1	null	null	Medium	1
  2	null	5	null	null	null	null
  3	1	null	1	null	Simple	1
  4	null	null	null	.5	null	null
  5	null	3	null	1	Medium	3

Issues with this:

1. When I try to pivot "Complexity Counts 1.2" I get an error that columns "Medium" and "Simple" already exist. I would like for this to pivot these numbers into the existing columns but clearly it doesn't work like that.
2. I don't know how many Complexities are going to be in each column. While the example I show only has 1-2 complexities per project, they might have more. So I would have to do this power query forumla on an unknown number of columns after separating by the comma delimeter.

Dax:

• I tried in Dax just creating a custom column for each complexity type, like
  • Medium = IF(CONTAINSSTRING('Project Tracker'[Complexity Counts], "Medium"), True, False)
• Issues with this:
  • I cannot figure out how to get the actual count instead of just True or False. A column with "Medium (2), Simple (1)", should return a 2.
  • New complexities arise all the time. They may not always be Mini, Simple, Medium, and Large. So I would have to go in and create a new column anytime a new complexity shows up in the spreadsheet.

Any and all help is appreciated. Thank you!

[ImkeF · Accepted solution]

Hi @kboscar1 

Please paste the following code into the advanced editor and follow the steps. I've broken them up into digestible steps (hopefully):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOzC3ISVXQMNLUUfBNTckszVXQMNRUitWJVjICSsOETCFCxkAhn8Si9FSQIh0FmGaoBhOQhsy8TAUNPah6U7iIIZL5xkDJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Number" = _t, #"Complexity Counts" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Number", Int64.Type}, {"Complexity Counts", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Complexity Counts], "," )),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Trimmed Text" = Table.TransformColumns(#"Expanded Custom",{{"Custom", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Custom", Splitter.SplitTextByDelimiter(" (", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"Custom.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Complexity Counts"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "Custom.2")
in
    #"Pivoted Column"

 ... also enclosing the file

[Tad17]

Hey @kboscar1 

Your final screenshot looks correct except that it has the two extra columns. Can't you just delete those columns once you are at the point or create a reference query and the use the formulas outlined in this thread to replicate the index/match feature with a LOOKUPVALUE: https://community.powerbi.com/t5/Desktop/power-bi-quot-Index-Match-quot/td-p/119419

?