Forum Discussion
Create collumn based on another
Dear Sir's
Good morning,
I have this table with collumn "TAGS", this collumn have multiple values in same line separated by a comma. I have the "Location" (example: LX , PO, BE, or GR ) and the number of the "Room" (529,501,etc) values that can be repeated more than once.
I want to create a 2 custom columns , one for "Location" and another one for "Room Number" ,
But in the TAGS column I have the Location sometimes in the first LOCATION, ROOM NUMBER and other times I have first ROOM NUMBER , LOCATION , how can I create a condition to create these two columns and input the correct value in each of them?
| TAGS |
| LX,529 |
| LX,501 |
| LX,512A |
| 522A,LX |
| 539,LX |
| LX,521B |
| LX,Anfiteatro3 |
| LX,132 |
| 512B,LX |
| 511,LX |
| 323,LX |
| 515,LX |
| 232,LX |
| 516,LX |
| 513,LX |
| 19,LX |
| 531,LX |
| LX,514 |
| LX,441 |
| LX,521 |
| SalPo,LX |
| 134,PO |
| Auditorio1,LX |
| LX,Auditorio1 |
| 3511,LX |
| 520A,LX |
| 533,PO |
| 520D,LX |
| 537,LX |
| 512,LX |
| 528,LX |
| SD1,LX |
| 538,LX |
| 529,LX |
| 536,LX |
| Anfiteatro2,LX |
| EAA002,LX |
| 422,LX |
| EAA005,LX |
| 526,LX |
| 527,LX |
| SalaEM,LX |
| 522B,LX |
| EA226,LX |
| EP004,LX |
| LX,144 |
| LX,424 |
| LX,518 |
| LX,255 |
| AuditorioA3,LX |
| 525,LX |
| 521A,LX |
| 5408,LX |
| 517,LX |
| 6,PO |
| 520B,LX |
| LX,146 |
| LX,ER207 |
| LX,135 |
Thank you for your help and attention.
1 Reply
Hey Jan845 ,
I recommend you to use power query and try the following based on your data:
- split by delimeter- define Location as a column which does NOT contain digits or starts with "Sal" (you may have anohter exceptions)
- define Room as column which does contain digit or starts with "Sal"
I used Text.Select([TAGS.1], {"0".."9"}) = "" condition to look for digits, you may find another approach.
Here is the code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZJBi4MwEIX/i2cPmcnE6jHSsJcuK+seCtKDsC0IpUJx//+KMvPi7fMleXnz4jAUP/GjL27lUFyuZeDG0JEhcdw4MMfyct3ZN4rbQWqV4+sxLfdxec9eJfK8HyJuzYBI0bOHGhTZM9QKaHupQRjKw5AoilAWccN+fHazWXgpu68N49/vtMzvac6tIO5Bs9CBXVaGV5tVPkM+ITaG4VqxP8PO19iQDWaDo1ZzSjE6Z1/Cx4UAP7THlmjtYUyfWMDLpMg4kTrnJGuEBOWyoPJakUM49hnxuJxlIrQnDrOTBayySttDgkoxfbM74SdbL779Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TAGS", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "TAGS", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"TAGS.1", "TAGS.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TAGS.1", type text}, {"TAGS.2", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Location", each if Text.StartsWith([TAGS.1], "Sal") or Text.Select([TAGS.1], {"0".."9"}) <> "" then [TAGS.2] else [TAGS.1]), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Room", each if Text.StartsWith([TAGS.1], "Sal") or Text.Select([TAGS.1], {"0".."9"}) = "" then [TAGS.2] else [TAGS.1]) in #"Added Conditional Column1"
