Solved: Re: Fix splitting of columns based on cell values

Sohaib · ‎03-20-2024

I have a column which needs to be split into fixed 10 columns but the data in row dynamically changes. Means it does not have all the data of 10 columns so in this case if the particular column value does not present in the row it needs to be zero in the column and if any value present it will be shown. E.g (1:3232:2:2322:3:4343:4:77:5:6565:.......etc till 10:2123) this data present in the row it needs to be split. "1:3232" represents the 1st column has 3232 value, similarly "2:2322" represents the 2nd column has 2322 value. But the catch is that data is not simple, it is like e.g(2:3232:6:2212). This means it has only 2 column values for column number 2nd and 6. The rest of the column needs to be zero value and sometimes it is like (1:2312:2:323:6:2111:8:21312) How can I split it? Please help me.

Table

Column

1:788:5:237:6:61:7:9:9:3034454

1:1:5:95:6:2381472:7:6329404:8:517

1:754:5:187:6:749381:7:4298716:8:98:9:93

1:47:5:117:6:789068:7:18874531:8:250536:9:69263

1:45:6:22234:7:8124606:8:878

The desired result requires is below

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10
0	0	0	0	1079	479567	43046	184101	3	0
0	0	0	0	0	555725	6017	3889885	0	0
788	0	0	0	237	61	9	0	9	0
1	0	0	0	95	2381472	6329404	517	0	0
754	0	0	0	187	749381	4298716	98	93	0
47	0	0	0	117	789068	18874531	250536	69263	0
45	0	0	0	0	22234	8124606	878	0	0

dufoq3 · ‎03-20-2024

Hi @Sohaib,

Result

v1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc7LDcRACAPQXua8hwHMt5Uo/bcRTxStOCGeLa5ryWTV+KjlxATXaY5tAxzr/h0iBO28q5UglShMGxvDrOTH0kEodZoSTUsI7UoJwq5TbR9GHiuvrd5RtFKVcBNi9e0WDERr/DPvD6oG4hJF7FNcWeu+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_ToTable = Table.AddColumn(Source, "ToTable", each 
        [ a = List.Split(Text.Split([Column1], ":"), 2),
          b = #table(List.Transform({ 1..10 }, (x)=> "Column" & Text.From(x)), { List.Repeat({null}, 10) }),
          c = Table.Skip(b & #table(List.Transform(a, (x)=> "Column" & x{0}), { List.Transform(a, (x)=> x{1}) }))
        ][c], type table),
    ToTable = Table.Combine(Ad_ToTable[ToTable]),
    ReplacedValue = Table.ReplaceValue(ToTable,null,"0",Replacer.ReplaceValue, Table.ColumnNames(ToTable))
in
    ReplacedValue

v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc7LDcRACAPQXua8hwHMt5Uo/bcRTxStOCGeLa5ryWTV+KjlxATXaY5tAxzr/h0iBO28q5UglShMGxvDrOTH0kEodZoSTUsI7UoJwq5TbR9GHiuvrd5RtFKVcBNi9e0WDERr/DPvD6oG4hJF7FNcWeu+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ToTable = List.TransformMany(
        Table.ToRows(Source),
        each {List.Split(Text.Split(_{0}, ":"), 2)},
        (x,y)=>  #table(List.Transform(y, each "Column" & _{0}), {List.Transform(y, each _{1})}) ),
    Result = [ a = #table(List.Transform({ 1..10 }, (x)=> "Column" & Text.From(x)), { List.Repeat({null}, 10) }),
    b = Table.Skip(a & Table.Combine(ToTable))
  ][b],
    ReplacedValue = Table.ReplaceValue(Result,null,"0",Replacer.ReplaceValue, Table.ColumnNames(Result))
in
    ReplacedValue

v3

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc7LDcRACAPQXua8hwHMt5Uo/bcRTxStOCGeLa5ryWTV+KjlxATXaY5tAxzr/h0iBO28q5UglShMGxvDrOTH0kEodZoSTUsI7UoJwq5TbR9GHiuvrd5RtFKVcBNi9e0WDERr/DPvD6oG4hJF7FNcWeu+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_ToTable = Table.AddColumn(Source, "ToTable", each 
        [ a = List.Split(Text.Split([Column1], ":"), 2),
          b = List.Accumulate({1..10}, #table({"Col0"}, {{null}}), (s,c)=> Table.AddColumn(s, "Column" & Text.From(c), (x)=> if Text.From(c) = List.Select(a, (x)=> x{0} = Text.From(c)){0}?{0}? then Number.From(List.Select(a, (x)=> x{0} = Text.From(c)){0}?{1}?) else 0, Int64.Type)),
          c = Table.RemoveColumns(b, {"Col0"})
        ][c], type table),
    ToTable = Table.Combine(Ad_ToTable[ToTable])
in
    ToTable

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Sohaib · ‎03-20-2024

pmPdcpVolDlDrbQci, pmPdcpVolDlDrbLastTTIQci, pmDrbThpTimeDlQci, pmSessionTimeDrbQci, pmErabRelAbnormalEnbQci, pmErabRelNormalEnbQci, pmErabRelMmeQci, pmErabEstabSuccAddedQci, pmErabEstabSuccInitQci, pmErabEstabAttInitQci, pmErabEstabAttAddedQci, pmErabEstabAttAddedHoOngoingQci.

there are actually 12 columns which names mentioned above need to be split in their respective 1 to 9 (9 columns)

dufoq3 · ‎03-21-2024

Hi @Sohaib,

for future requests, think in advance what exactly do you need please.

Specify columns which you want to split in this step (just separate that column names with coma or coma and space)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/JEYMwEETRXHTmoFk0i25sUVDkn4ZbIgBjFyeK99Xiusq6lqVQ94jeOot364bXnnikimpTgDfqXq6ybdMRVDYgliB1hjTh1KodB5ADvZXj1H1/1ptCU4x110QArZzhZNAZ4zoC+psfC8cxC/UR0AwiqwUCinBtQii41SaGypJtDP2Vjb3zfML558yiKIJYrY6bhQc+fzf3/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SomeText = _t, pmPdcpVoIDIDrbQciworking = _t, SomeTex2 = _t, AsdfGh = _t]),
    TrimColumnNames = Table.TransformColumnNames(Source, Text.Trim),
    ColumnsToSplit = "pmPdcpVoIDIDrbQciworking, AsdfGh",
    ColumnsList = List.Transform(Text.Split(ColumnsToSplit, ", "), Text.Trim),
    ToTable = 
        List.Accumulate(
            ColumnsList,
            TrimColumnNames,
            (s,cur)=> Table.AddColumn(s, cur & "_Table", each 
                      [ a = List.Split(Text.Split(Record.Field(_, cur), ":"), 2),
                        b = #table(List.Transform({ 1..10 }, (x)=> cur & Text.From(x)), { List.Repeat({null}, 10) }),
                        c = Table.Skip(b & #table(List.Transform(a, (x)=> cur & x{0}), { List.Transform(a, (x)=> x{1}) }))
                      ][c], type table)
        ),
    ExpandNewTables = [ a = List.Combine(List.Transform(ColumnsList, each Table.ToColumns(Table.Combine(Table.Column(ToTable, _ & "_Table"))))), //New Tables as list of columns
    b = Table.ToColumns(TrimColumnNames), //Original table as list of columns
    c = List.Combine(List.Transform(ColumnsList, each Table.ColumnNames(Record.Field(Table.SelectColumns(ToTable, _ & "_Table"){0}, _ & "_Table")))), //New Splitted Column Names
    d = Table.FromColumns(b & a, Table.ColumnNames(TrimColumnNames) & c) //Merge original table with new splitted columns
  ][d]
in
    ExpandNewTables

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Sohaib · ‎03-20-2024

for the other columns their respective name contain the same name with end on .1 .2 and .9 etc

Sohaib · ‎03-20-2024

the 10 column names mean the one is pmPdcpVolDlDrbQci and the 10th one is pmPdcpVolDlDrbQci.9

dufoq3 · ‎03-20-2024

Hi @Sohaib,

Result

v1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc7LDcRACAPQXua8hwHMt5Uo/bcRTxStOCGeLa5ryWTV+KjlxATXaY5tAxzr/h0iBO28q5UglShMGxvDrOTH0kEodZoSTUsI7UoJwq5TbR9GHiuvrd5RtFKVcBNi9e0WDERr/DPvD6oG4hJF7FNcWeu+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_ToTable = Table.AddColumn(Source, "ToTable", each 
        [ a = List.Split(Text.Split([Column1], ":"), 2),
          b = #table(List.Transform({ 1..10 }, (x)=> "Column" & Text.From(x)), { List.Repeat({null}, 10) }),
          c = Table.Skip(b & #table(List.Transform(a, (x)=> "Column" & x{0}), { List.Transform(a, (x)=> x{1}) }))
        ][c], type table),
    ToTable = Table.Combine(Ad_ToTable[ToTable]),
    ReplacedValue = Table.ReplaceValue(ToTable,null,"0",Replacer.ReplaceValue, Table.ColumnNames(ToTable))
in
    ReplacedValue

v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc7LDcRACAPQXua8hwHMt5Uo/bcRTxStOCGeLa5ryWTV+KjlxATXaY5tAxzr/h0iBO28q5UglShMGxvDrOTH0kEodZoSTUsI7UoJwq5TbR9GHiuvrd5RtFKVcBNi9e0WDERr/DPvD6oG4hJF7FNcWeu+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ToTable = List.TransformMany(
        Table.ToRows(Source),
        each {List.Split(Text.Split(_{0}, ":"), 2)},
        (x,y)=>  #table(List.Transform(y, each "Column" & _{0}), {List.Transform(y, each _{1})}) ),
    Result = [ a = #table(List.Transform({ 1..10 }, (x)=> "Column" & Text.From(x)), { List.Repeat({null}, 10) }),
    b = Table.Skip(a & Table.Combine(ToTable))
  ][b],
    ReplacedValue = Table.ReplaceValue(Result,null,"0",Replacer.ReplaceValue, Table.ColumnNames(Result))
in
    ReplacedValue

v3

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc7LDcRACAPQXua8hwHMt5Uo/bcRTxStOCGeLa5ryWTV+KjlxATXaY5tAxzr/h0iBO28q5UglShMGxvDrOTH0kEodZoSTUsI7UoJwq5TbR9GHiuvrd5RtFKVcBNi9e0WDERr/DPvD6oG4hJF7FNcWeu+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_ToTable = Table.AddColumn(Source, "ToTable", each 
        [ a = List.Split(Text.Split([Column1], ":"), 2),
          b = List.Accumulate({1..10}, #table({"Col0"}, {{null}}), (s,c)=> Table.AddColumn(s, "Column" & Text.From(c), (x)=> if Text.From(c) = List.Select(a, (x)=> x{0} = Text.From(c)){0}?{0}? then Number.From(List.Select(a, (x)=> x{0} = Text.From(c)){0}?{1}?) else 0, Int64.Type)),
          c = Table.RemoveColumns(b, {"Col0"})
        ][c], type table),
    ToTable = Table.Combine(Ad_ToTable[ToTable])
in
    ToTable

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Sohaib · ‎03-20-2024

Really appericiate your reply but things i need to know where i can add this code like i have column which i need to be split between the table so. Do i need to add custom column and paste that code there.

dufoq3 · ‎03-20-2024

You should read note below my posts
In your sample data there are not any other columns...

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Sohaib · ‎03-20-2024

Sorry I have not seen Notes below. So i need to extract this column other table and run this query and again joint them. I guess this is the solution. Thanks @dufoq3

dufoq3 · ‎03-20-2024

No, I will update my query. Is marked column only one you want to split? What about new column names - do you want to preserve Column1, Column2 etc. or som different names?

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

dufoq3 · ‎03-20-2024

@Sohaib,

you can choose new column name prefix here:

v1 (this one should be the fastest)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jc67DcAgDEXRXahT4L9Nl98UUfZfIw8iKkv32DxP2/e2NRqROWywxPDhGEfhSRdV0/ZuTzuO1RGqMkQsSRqM0oVLuw4soFjtef47TVFTzp2hBYBauTLIUVfOI7LEdS2hMQEtkNU9ASgz1IQg2LqJQ3mx//C+f7i+xCwKkcTqfZ7IyPa+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SomeText = _t, pmPdcpVoIDIDrbQciworking = _t]),
    ColumnPrefix = "NewCol_",
    Ad_ToTable = Table.AddColumn(Source, "ToTable", each 
        [ a = List.Split(Text.Split([pmPdcpVoIDIDrbQciworking], ":"), 2),
          b = #table(List.Transform({ 1..10 }, (x)=> ColumnPrefix & Text.From(x)), { List.Repeat({null}, 10) }),
          c = Table.Skip(b & #table(List.Transform(a, (x)=> ColumnPrefix & x{0}), { List.Transform(a, (x)=> x{1}) }))
        ][c], type table),
    AddedColumns = List.Select(Table.ColumnNames(Ad_ToTable{0}[ToTable]), each Text.StartsWith(_, ColumnPrefix)),
    Expanded = Table.ExpandTableColumn(Ad_ToTable, "ToTable", AddedColumns),
    ReplacedValue = Table.ReplaceValue(Expanded,null,"0",Replacer.ReplaceValue, AddedColumns)
in
    ReplacedValue

v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jc67DcAgDEXRXahT4L9Nl98UUfZfIw8iKkv32DxP2/e2NRqROWywxPDhGEfhSRdV0/ZuTzuO1RGqMkQsSRqM0oVLuw4soFjtef47TVFTzp2hBYBauTLIUVfOI7LEdS2hMQEtkNU9ASgz1IQg2LqJQ3mx//C+f7i+xCwKkcTqfZ7IyPa+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SomeText = _t, pmPdcpVoIDIDrbQciworking = _t]),
    ColumnPrefix = "NewCol_",
    ToTable = List.TransformMany(
        Table.ToRows(Table.SelectColumns(Source, {"pmPdcpVoIDIDrbQciworking"})),
        each {List.Split(Text.Split(_{0}, ":"), 2)},
        (x,y)=>  #table(List.Transform(y, each ColumnPrefix & _{0}), {List.Transform(y, each _{1})}) ),
    Result = [ a = #table(List.Transform({ 1..10 }, (x)=> ColumnPrefix & Text.From(x)), { List.Repeat({null}, 10) }),
    b = Table.Skip(a & Table.Combine(ToTable))
  ][b],
    MergedResultSource = Table.FromColumns(Table.ToColumns(Source) & Table.ToColumns(Result), Table.ColumnNames(Source) & Table.ColumnNames(Result)),
    ReplacedValue = Table.ReplaceValue(MergedResultSource,null,"0",Replacer.ReplaceValue, List.Select(Table.ColumnNames(Result), each Text.StartsWith(_, ColumnPrefix)))
in
    ReplacedValue

v3

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc7LDcRACAPQXua8hwHMt5Uo/bcRTxStOCGeLa5ryWTV+KjlxATXaY5tAxzr/h0iBO28q5UglShMGxvDrOTH0kEodZoSTUsI7UoJwq5TbR9GHiuvrd5RtFKVcBNi9e0WDERr/DPvD6oG4hJF7FNcWeu+Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    ColumnPrefix = "NewCol_",
    Ad_ToTable = Table.AddColumn(Source, "ToTable", each 
        [ a = List.Split(Text.Split([Column1], ":"), 2),
          b = List.Accumulate({1..10}, #table({"Col0"}, {{null}}), (s,c)=> Table.AddColumn(s, ColumnPrefix & Text.From(c), (x)=> if Text.From(c) = List.Select(a, (x)=> x{0} = Text.From(c)){0}?{0}? then Number.From(List.Select(a, (x)=> x{0} = Text.From(c)){0}?{1}?) else 0, Int64.Type)),
          c = Table.RemoveColumns(b, {"Col0"})
        ][c], type table),
    AddedColumns = List.Select(Table.ColumnNames(Ad_ToTable{0}[ToTable]), each Text.StartsWith(_, ColumnPrefix)),
    Expanded = Table.ExpandTableColumn(Ad_ToTable, "ToTable", AddedColumns),
    ReplacedValue = Table.ReplaceValue(Expanded,null,"0",Replacer.ReplaceValue, AddedColumns)
in
    ReplacedValue

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Sohaib · ‎03-20-2024

no there are other columns as well which need to be split in similar fashion but their data also split in their respective columns, For the column names e.g need to be like pmPdcpVolDlDrbQci, pmPdcpVolDlDrbQci.1, pmPdcpVolDlDrbQci.2 etc...