Split by delimiter into rows while changing order

[pcda]

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 number of commas in it. Then I have a different column with their respective expiration date separated by commas, but in inverse order.

Batch A expires in 2022, Batch B in 2023, Batch C in 2024 and Batch D in 2025, my columns would look something like this:

Batch	Expiration
A,B,C,D	2025,2024,2023,2022
E,F	2025,2026

I will need it to look like this:

Batch	Expiration
A	2022
B	2023
C	2024
D	2025
E	2026
F	2025

Spliting column by delimiter into rows will do half of the job, since the Expiration and the batch would not match because of the inverse order. Also, the number of commas (or batches or dates) can be any from 0 to 1,000 in any given row. 

Anyone knows a way how to solve this?

[v-yalanwu-msft · Accepted solution]

Hi, @pcda ;

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.

[pcda]

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"