Forum Discussion
Split value to several rows
Hi, I am a bit new to Power BI and have tried to search for an solution to how I should split my values into several rows.
When get the data it looks like this
| ID | Tags |
| 1 | {A,B,C} |
2 | {A} |
| 3 | {} |
I would like it to be splitted like this
| ID | Tags |
| 1 | A |
1 | B |
| 1 | C |
| 2 | A |
| 3 |
|
I don't really know where to start.
Please refer :
https://www.youtube.com/watch?v=0roS4OVfD9k
This is in edit query mode
You also have to use :https://docs.microsoft.com/en-us/powerquery-m/text-replace
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
3 Replies
Please refer :
https://www.youtube.com/watch?v=0roS4OVfD9k
This is in edit query mode
You also have to use :https://docs.microsoft.com/en-us/powerquery-m/text-replace
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on LinkedinWow, that was easy. Thanks!
Please see a solution below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUap21HHSca5VitWJVjIC8yFsYxAbyIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Tags = _t]), #"Added Custom" = Table.AddColumn(Source, "New Tags", each Text.Split( Text.Remove( [Tags], {"{","}"} ), "," ) ), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "New Tags"}), #"Expanded New Tags" = Table.ExpandListColumn(#"Removed Other Columns", "New Tags"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded New Tags",{{"New Tags", type text}}) in #"Changed Type"Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
