Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Split by delimiter into rows while changing order

Hi everyone! I'm looking for a way to split two columns into rows but inverse the order in the second one.    I have one column with a batch number separated by commas, however there can be any num...
Need Help
Tips and Tricks
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, Anonymous ;

    You could try it in power query.

    Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRx0nHWcVHSUTIyMDLVARImIMIYRBgpxepEK7nquCHJminFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Batch = _t, Expiration = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Batch],",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Reverse(Text.Split([Expiration],","))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Zip({[Custom],[Custom.1]})),
    #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom.2", "Custom.3", each [Custom.2]{0}),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each [Custom.2]{1}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Batch", "Expiration", "Custom", "Custom.1", "Custom.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.3", "Batch"}, {"Custom.4", "Expiration"}})
in
    #"Renamed Columns"

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi, Anonymous ;

You could try it in power query.

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRx0nHWcVHSUTIyMDLVARImIMIYRBgpxepEK7nquCHJminFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Batch = _t, Expiration = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Batch],",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Reverse(Text.Split([Expiration],","))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Zip({[Custom],[Custom.1]})),
    #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom.2", "Custom.3", each [Custom.2]{0}),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each [Custom.2]{1}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Batch", "Expiration", "Custom", "Custom.1", "Custom.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.3", "Batch"}, {"Custom.4", "Expiration"}})
in
    #"Renamed Columns"

The final show:


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you so much! You are a genious 🙂 

     

    This is my "final" version with more detailed names (literally tomato, tomato) just in case it works for someone else:

     

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRx0nHWcVHSUTIyMDLVARImIMIYRBgpxepEK7nquCHJminFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Batch = _t, Expiration = _t]),
    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRx0nHWcVHSUTIyMDLVARImIMIYRBgpxepEK7nquCHJminFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Batch = _t, Expiration = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"ListBatch" = Table.AddColumn(#"Promoted Headers","ListBatch", each Text.Split([Batch],",")),
    #"ReverseExpiration" = Table.AddColumn(ListBatch, "ReverseExpiration", each List.Reverse(Text.Split([Expiration],","))),
    #"ZipList" = Table.AddColumn(ReverseExpiration, "ListOfLists", each List.Zip({[ListBatch],[ReverseExpiration]})),
    #"ExpandLOL" = Table.ExpandListColumn(#"ZipList","ListOfLists"),
    #"Final Batch" = Table.AddColumn(#"ExpandLOL", "FinalBatch", each [ListOfLists]{0}),
    #"Final Expiration" = Table.AddColumn(#"Final Batch", "FinalExpiration", each [ListOfLists]{1}),
    #"Removed Columns" = Table.RemoveColumns(#"Final Expiration",{"Batch", "Expiration", "ListBatch", "ReverseExpiration", "ListOfLists"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"FinalBatch", "Batch"}, {"FinalExpiration", "Expiration"}})
in
    #"Renamed Columns"