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

[Sweet-T]

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, C	A	B	C
B,C	null	B	C
A,C	A	null	C

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, C	1	1	1
B,C	0	1	1
A,C	1	0	1

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

[MFelix · Accepted solution]

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

[Anonymous · Accepted solution]

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)"

[Ashish_Mathur · Accepted solution]

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.

[Sweet-T]

Clever, I like it. Thank you!

[Ashish_Mathur]

You are welcome.

[Anonymous · Accepted solution]

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)"

[Anonymous]

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"

[MFelix · Accepted solution]

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

[Sweet-T]

Same principal as Ashish, also works! Thank you.