Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pcda
Helper I
Helper I

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 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:

BatchExpiration
A,B,C,D2025,2024,2023,2022
E,F2025,2026

 

I will need it to look like this:

BatchExpiration

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?

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1663311352082.png


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.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1663311352082.png


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.

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"

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.