Forum Discussion

Jan845's avatar
Jan845
Icon for Helper I rankHelper I
3 years ago

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 ...
Need Help
alena2k's avatar
alena2k
Icon for Resolver IV rankResolver IV
3 years ago

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"