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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
escobardavila
Regular Visitor

Split column by delimiter and also relate them in the same order

Hi everybody!

I really appreciate your help to see how I can solve this. I have an excel file with this data:

 

Inicial.PNG

 

At the end I have to represent the data like this: for each line each serial

 

Datos finales.PNG

How can I do it?

I did a test: unpivot, but it's not what I need 😞

 

Unpivot.PNG

 

And another test with PO Line and Serial column split: but I can't get what I want to show either.

 

Split.PNG

 

I appreciate your ideas and support.

 

Regards,

 

Lina

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

lbendlin_0-1714436493402.png

This can be optimized, but gives you the general idea.  Note the inconsistency - PO Line values are only separated by comma, Serial values also have spaces.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYzBCsMwDEN/Zfjsge3EsXceYZdtDaUwRsn//8bs0UsFEpIOb9+hiN1sHf26fZ6AsK1SR78vlGoeT38tkVW9kCsLx0j39yASmJgIFz0jHt8DwXxGmJCWRKBgwYqKLZaZ4sUaR5hnq9n+n8KcPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DO Final" = _t, #"Reference / Subject e-mail" = _t, Priority = _t, #"SAP PO" = _t, #"PO Line" = _t, Serial = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([PO Line],","),Text.Split([Serial],",")})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Record.FromList([Custom],type [#"PO Line"=text, Serial=text])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"DO Final", "Reference / Subject e-mail", "Priority", "SAP PO", "Custom.1"}),
    #"Expanded Custom.1" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom.1", {"PO Line", "Serial"}, {"PO Line", "Serial"})
in
    #"Expanded Custom.1"

 

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

lbendlin_0-1714436493402.png

This can be optimized, but gives you the general idea.  Note the inconsistency - PO Line values are only separated by comma, Serial values also have spaces.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYzBCsMwDEN/Zfjsge3EsXceYZdtDaUwRsn//8bs0UsFEpIOb9+hiN1sHf26fZ6AsK1SR78vlGoeT38tkVW9kCsLx0j39yASmJgIFz0jHt8DwXxGmJCWRKBgwYqKLZaZ4sUaR5hnq9n+n8KcPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DO Final" = _t, #"Reference / Subject e-mail" = _t, Priority = _t, #"SAP PO" = _t, #"PO Line" = _t, Serial = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([PO Line],","),Text.Split([Serial],",")})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Record.FromList([Custom],type [#"PO Line"=text, Serial=text])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"DO Final", "Reference / Subject e-mail", "Priority", "SAP PO", "Custom.1"}),
    #"Expanded Custom.1" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom.1", {"PO Line", "Serial"}, {"PO Line", "Serial"})
in
    #"Expanded Custom.1"

 

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

 

@lbendlin Thank you so much! It worked 😊

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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