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 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 🙂
Solved! Go to 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.
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
No luck?
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.
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
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
Covering 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 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |