Filter a column into a new column based on text.

[Malone]

Hi power BI wizards,

If I have a column from a table that has been filled with various jumbled inputed data how do I then take the relevant data and filter into new columns based on specified text. 

For instance say the table cells in the column are: "rock", "paper", "scissors", "lets go rock", "paper sucks", "rock always wins", "come on scissors", "I like paper". How do I then going and take that data and turn it into new columns that just say.

Col1:      Col2:       Col3: 

Rock       Paper     Scissors

Rock       Paper     Scissors

Rock       Paper

So basically I can filter based on the term and have the irrelevant date ommitted so I can display in a simple bar graph. 

Appreciate any help 🙂 

[Malone]

No luck?

[GilbertQ]

Hi there I started to have a look, but it becomes rather difficult when all the data is stored within one column.

And then take that data, which can be put into 3 separate columns, but to try and bring them all back into the one line with the 3 columns was a challenge I did not have time to complete yet.

[ImkeF · Accepted solution]

Pls check out this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU1LCoAgFLzK4LqLdAZxIfII0XziFNLtI2sj7eY/1pqmIRm3WFN9lTYQQyS1cZAsB7Ep5hx4hkQM4XHgc/cX0WN5a0F3gRZMWytyTIL/07fubg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Column1], " ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom",{"Custom"},Source,{"Column1"},"Expanded Custom",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Expanded Custom", "Column1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"Columns", each _[Custom], type table}}),
    Custom1 = Table.FromColumns(#"Grouped Rows"[Columns])
in
    Custom1

Also check if my addition of "scissors rock" at the end of the sample data deliver what you expect.

[GilbertQ]

For your example above what is the format of your source data?

Is it all in one cell?

[Malone]

Apologies should have been more clearer, no it's all in one column of the table as text data. So for instance it would look like this.

Example Column:

rock

paper

scissors

lets go rock

paper sucks

rock always wins

come on scissors

I like paper