Forum Discussion

Sohaib's avatar
Sohaib
Icon for Helper II rankHelper II
2 years ago
Solved

Fix 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...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

Sohaib's avatar
Sohaib
Icon for Helper II rankHelper II
2 years ago

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's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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