Reply
Member
Posts: 46
Registered: ‎07-09-2018
Accepted Solution

Split by Delimiter, but place values in columns with like values

[ Edited ]

Hi everyone: 

 

I'd like to split a column via a delimiter, and only sort the values into rows with like values. 

 

Example: 

Treatment Type
(to be split)
Treament A
(desired)

Treatment B

(desired)

Treatment C

(desired)

A, B, CABC
B,CnullBC
A,CAnullC

 

Additionally, I will then be converting this into a binary TRUE/FALSE. If I'm able to do that in one step, it would be great. Example below: 

Treatment Type (to be split)

Treament A

(desired)

Treatment B

(desired)

Treatment C

(desired)

A, B, C111
B,C011
A,C101


Either DAX or Query, doesn't matter to me!
Let me know if you've encountered something similar.
Thanks!


Accepted Solutions
Super User
Posts: 2,233
Registered: ‎09-19-2016

Re: Split by Delimiter, but place values in columns with like values

Hi @Sweet-T,

 

Not in a single step but try the coding below:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRx0nFWitWJVoLRjiA6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ttt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ttt", type text}}),
    #"Duplicated Column" = Table.AddColumn(#"Changed Type", "ttt - Copy", each [ttt], type text),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "ttt - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ttt - Copy.1", "ttt - Copy.2", "ttt - Copy.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ttt - Copy.1", type text}, {"ttt - Copy.2", type text}, {"ttt - Copy.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ttt"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each 1),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Value]), "Value", "Custom", List.Sum)
in
    #"Pivoted Column"

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Super User
Posts: 1,830
Registered: ‎02-28-2017

Re: Split by Delimiter, but place values in columns with like values

I'd follow these steps:

 

Split column by delimiter, just like you have done.

Unpivot the data

Do an appead onto the data so that you wrap "Treatment " and " (desired)" around the values.

Add a new valuescolumn with a 1 in it.

Pivot the data

 

All this can be done within the Edit Queries section, most of it via the buttons in the ribbon menu.

 

To wrap the text with those extra items, just add a column and use a formula like

= "Treatment " & [TheColumn] & " (desired)"


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Super User
Posts: 3,938
Registered: ‎01-14-2017

Re: Split by Delimiter, but place values in columns with like values

Hi,

 

I have solved it using Power Query in Excel.  You may replicate the same in PBI desktop as well.   Download the Excel file from here.

View solution in original post


All Replies
Super User
Posts: 2,233
Registered: ‎09-19-2016

Re: Split by Delimiter, but place values in columns with like values

Hi @Sweet-T,

 

Not in a single step but try the coding below:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRx0nFWitWJVoLRjiA6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ttt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ttt", type text}}),
    #"Duplicated Column" = Table.AddColumn(#"Changed Type", "ttt - Copy", each [ttt], type text),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "ttt - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ttt - Copy.1", "ttt - Copy.2", "ttt - Copy.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ttt - Copy.1", type text}, {"ttt - Copy.2", type text}, {"ttt - Copy.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ttt"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each 1),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Value]), "Value", "Custom", List.Sum)
in
    #"Pivoted Column"

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Posts: 1,830
Registered: ‎02-28-2017

Re: Split by Delimiter, but place values in columns with like values

I'd follow these steps:

 

Split column by delimiter, just like you have done.

Unpivot the data

Do an appead onto the data so that you wrap "Treatment " and " (desired)" around the values.

Add a new valuescolumn with a 1 in it.

Pivot the data

 

All this can be done within the Edit Queries section, most of it via the buttons in the ribbon menu.

 

To wrap the text with those extra items, just add a column and use a formula like

= "Treatment " & [TheColumn] & " (desired)"


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Super User
Posts: 1,830
Registered: ‎02-28-2017

Re: Split by Delimiter, but place values in columns with like values

[ Edited ]

Here is a quick sample that should give you a clue of what I was suggesting:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRRcNJRcFaK1YlWctKB0I4gOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Treatment Type" = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Treatment Type", "Treatment Type Original"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Treatment Type Original", "Treatment Type"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Treatment Type", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Treatment Type.1", "Treatment Type.2", "Treatment Type.3", "Treatment Type.4", "Treatment Type.5", "Treatment Type.6", "Treatment Type.7", "Treatment Type.8", "Treatment Type.9", "Treatment Type.10", "Treatment Type.11", "Treatment Type.12", "Treatment Type.13", "Treatment Type.14", "Treatment Type.15", "Treatment Type.16", "Treatment Type.17", "Treatment Type.18", "Treatment Type.19", "Treatment Type.20", "Treatment Type.21", "Treatment Type.22", "Treatment Type.23", "Treatment Type.24", "Treatment Type.25", "Treatment Type.26", "Treatment Type.27", "Treatment Type.28", "Treatment Type.29", "Treatment Type.30", "Treatment Type.31", "Treatment Type.32", "Treatment Type.33", "Treatment Type.34", "Treatment Type.35", "Treatment Type.36", "Treatment Type.37", "Treatment Type.38", "Treatment Type.39", "Treatment Type.40", "Treatment Type.41", "Treatment Type.42", "Treatment Type.43", "Treatment Type.44", "Treatment Type.45", "Treatment Type.46", "Treatment Type.47", "Treatment Type.48", "Treatment Type.49", "Treatment Type.50"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Treatment Type Original"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"}),
    #"Clean heading name" = Table.RenameColumns(#"Removed Columns",{{"Treatment Type Original", "Treatment Type"}}),
    #"Added Custom" = Table.AddColumn(#"Clean heading name", "Headers", each "Treatment " & [Value] & " (desired)"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Added Value" = Table.AddColumn(#"Removed Columns1", "Value", each 1),
    #"Pivoted Column" = Table.Pivot(#"Added Value", List.Distinct(#"Added Value"[Headers]), "Headers", "Value", List.Sum),
    #"Remove nulls" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Treatment A (desired)", "Treatment B (desired)", "Treatment C (desired)"})
in
    #"Remove nulls"

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Super User
Posts: 3,938
Registered: ‎01-14-2017

Re: Split by Delimiter, but place values in columns with like values

Hi,

 

I have solved it using Power Query in Excel.  You may replicate the same in PBI desktop as well.   Download the Excel file from here.

Member
Posts: 46
Registered: ‎07-09-2018

Re: Split by Delimiter, but place values in columns with like values

Clever, I like it. Thank you!

Member
Posts: 46
Registered: ‎07-09-2018

Re: Split by Delimiter, but place values in columns with like values

Same principal as Ashish, also works! Thank you.
Super User
Posts: 3,938
Registered: ‎01-14-2017

Re: Split by Delimiter, but place values in columns with like values

You are welcome.