How to split column into rows by Nth element

[Anastasia_007]

Dears, could you please help me to find a way to split a DEPARTMENT column into rows:

From This:

USER DEPARTMENT

USER1 a:3:{i:0;i:2;i:1;i:15;i:2;i:18;}

To this:

USER DEPARTMENT

USER1  2

USER1  15

USER1  18

where a:3 means that there are 3 departments: 2, 15, 18, accordingly;

i:0;i:2; - means first element with Id=2

i:1;i:15; - means second element with Id=15

I:2;i:18;  - means 3rd element with Id=18

Number of departments may be different from row to row.

For users with no departments it looks like:

a:0:{}

For users with only one departmnet (Id=2):

a:1:{i:0;i:2;} and so on.

Any help is appreciated!

Thank you in advance!

[Anastasia_007]

Dears, I have found a way:

1) Transform by Text.BetweenDelimiters by "{" and "}"

2) Text.Split by ";"

3) Table.Expandlist.Column

4) Table.AddIndexColumn

5) filter by IsEven([Index]) =true function.

[Anonymous · Accepted solution]

Hi @Anastasia_007 ,

You can try my way as well.

1) Transform by Text.BetweenDelimiters by "{" and "}"

2) Replace "i:" by ""

3) Text.Split by ";" by row

4) Remove blank by filter.

4) Add an Index and filter by Number.Mod().

Whole M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg12DTJU0lFKtDK2qs60MrDOtDICYkMQNoVxLKxrlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [USER = _t, DEPARTMENT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"USER", type text}, {"DEPARTMENT", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.BetweenDelimiters([DEPARTMENT],"{","}")),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","i:","",Replacer.ReplaceText,{"Custom"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Added Index", each (Number.Mod([Index],2) = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Index", "DEPARTMENT"})
in
    #"Removed Columns"

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anastasia_007]

Thank you for solution!