Forum Discussion

Anastasia_007's avatar
Anastasia_007
Regular Visitor
3 years ago
Solved

How to split column into rows by Nth element

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!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

3 Replies

  • Anastasia_007's avatar
    Anastasia_007
    Regular Visitor
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.