Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI'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)"
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.
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.
Clever, I like it. Thank you!
You are welcome.
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)"
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"
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |