Can I split the column into row with source and destination?

[kmkwongaa]

I have the above column. I want to split the string into row.

I know that I could split it by delimiter.

But I want to specify the source and destination for the path. 

e.g. for first row, [1_2_3_11_9_14_13_14_3_14_15_23_30_31_32],

I would like to split to the following format:

From    To

1            2

2            3

3            11 

11           9

etc

so that I can visualize the path with network plot.

How can I do this?

[v-eachen-msft · Accepted solution]

Hi @kmkwongaa ,

You could use split to two tables :one has cleared the first value and another one has cleared the last value. Then merge or combine these two tables.

You could refer to the following codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMow3ijeON4k3VYrViVYyiTeLt1SKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.2"}),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Column1.1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1.1"),
    #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter2", "Index", 0, 1),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column1.1"}),
    #"Split Column by Delimiter3" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns1", {{"Column1.2", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1.2"),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each #"Split Column by Delimiter3"[[Column1.2]]{[Index]}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Column1.2"}, {"Custom.Column1.2"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Column1.1", "From"}, {"Custom.Column1.2", "To"}})
in
    #"Renamed Columns"

Here is the sample and result.

[amitchandak]

@kmkwongaa , refer if this can help

https://www.youtube.com/watch?v=Jv1GQM3QwMY

https://www.sumproduct.com/blog/article/power-bi-tips/power-bi-new-transform-split-column-by-positio...