Forum Discussion
Split delimited cell values into columns
Greetings everyone,
Can you please help me with slitting cell values into columns per the following requirements?
The dataset looks similar to this:
| Cost | Tags |
| 100 | CostCenter:CC123456,Department:Sales |
| 200 | Department:Sales,CostCenter:CC345678 |
| 300 | Owner:Arif Mahmood,Department:Sales,CostCenter:R1234353 |
| 300 | App:Robocopy,Department:Marketing,CostCenter:CC098923 |
I am looking to convert it into this:
| Cost | Department | CostCenter |
| 100 | Sales | CC123456 |
| 200 | Sales | CC345678 |
| 300 | Sales | R1234353 |
| 300 | Marketing | CC098923 |
My "Tags" column contains delimited values. First by colon ":" and then by comman ",". If the tags column was all arranged where Department and CostCenter were in order then I could have used Split Column in Power Query editor. However, the it won't work the cell has different pairs at different places. I hope it makes sense as to what I am trying to achieve?
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
4 Replies
- PhilipTreacy
Super User
Hi anaib
Download PBIX file with the code below
Try this, works for me
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRcs4vLnFOzStJLbJydjY0MjYxNdNxSS1ILCrJBYpaBSfmpBYrxepEKxmBlaNL6aDoB+k2twArNwYr9y/PA0o4FmWmKfgmZuTm56dgGI5sQhDIAcamxkgmOBYUWAXlJ+Un5xdUIuv1TSzKTi3JzEtHdYGBpYWlEVB/LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cost = _t, Tags = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost", Int64.Type}, {"Tags", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Department", each if Text.Contains(Text.AfterDelimiter([Tags] , "Department:"), ",") then Text.BeforeDelimiter(Text.AfterDelimiter([Tags] , "Department:"), ",") else Text.AfterDelimiter([Tags] , "Department:")), #"Added Custom" = Table.AddColumn(#"Added Custom1", "CostCenter", each if Text.Contains(Text.AfterDelimiter([Tags] , "CostCenter:"), ",") then Text.BeforeDelimiter(Text.AfterDelimiter([Tags] , "CostCenter:"), ",") else Text.AfterDelimiter([Tags] , "CostCenter:")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Tags"}) in #"Removed Columns"Regards
Phil
- Ahmedx
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Thank you, Ahmed. Worked beautifully.
Hi anaib ,
Your solutions are great, Ahmedx and PhilipTreacy . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.
Here are the steps you can follow：
1. Create calculated column.
Department = var _len1= SEARCH( "Department",'Table'[Tags]) var _right= RIGHT('Table'[Tags], LEN('Table'[Tags])-_len1+1) var _len2= SEARCH( ",",_right,1,0) var _if= IF( _len2=0,_right, LEFT( _right, _len2-1)) var _len3= SEARCH(":",_if) return RIGHT( _if,LEN(_if)-_len3)CostCenter = var _len1= SEARCH( "CostCenter",'Table'[Tags]) var _right= RIGHT('Table'[Tags], LEN('Table'[Tags])-_len1+1) var _len2= SEARCH( ",",_right,1,0) var _if= IF( _len2=0,_right, LEFT( _right, _len2-1)) var _len3= SEARCH(":",_if) return RIGHT( _if,LEN(_if)-_len3)2. Create calculated table.
Table 2 = SUMMARIZE('Table',[Cost],[Department],[CostCenter])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
