How to split column into rows by Nth element

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.