Create collumn based on another

[Jan845]

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.

[alena2k]

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"