Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kboscar1
Frequent Visitor

Splitting and pivoting comma separated column

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 NumberComplexity Counts
1Simple (2), Medium (1)
2Medium (5)
3Large (1), Simple (1)
4Mini (.5)
5Mini (1), Medium (3)

 

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

Project NumberSimpleMediumLargeMini
12100
20500
31010
4000.5
50301

 

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.2Complexity Counts 1.2.1

    1

    Simple

    2

    Medium1

    2

    Medium

    5

    nullnull

    3

    Large 

    1

    Simple1

    4

    Mini

    .5

    nullnull

    5

    Mini

    1

    Medium 3
  • Pivot "Complexity Counts 1.1" into Complexity Counts 1.1.1
  • Project

    SimpleMediumLargeMiniComplexity Counts 1.2Complexity Counts 1.2.1

    1

    21nullnullMedium1

    2

    null5nullnullnullnull

    3

    1null1nullSimple1

    4

    nullnullnull.5nullnull

    5

    null3null1Medium 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!
1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Tad17
Solution Sage
Solution Sage

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

 

?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.