Forum Discussion
Helper IIFix splitting of columns based on cell values
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 |
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 ReplacedValuev2
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 ReplacedValuev3
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
11 Replies
- dufoq3
Community Champion
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 ReplacedValuev2
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 ReplacedValuev3
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
SohaibReally 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
- You should read note below my posts
- In your sample data there are not any other columns...
- Sohaib
the 10 column names mean the one is pmPdcpVolDlDrbQci and the 10th one is pmPdcpVolDlDrbQci.9
- Sohaib
for the other columns their respective name contain the same name with end on .1 .2 and .9 etc
- Sohaib
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
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