Forum Discussion

psvdr's avatar
psvdr
Frequent Visitor
2 years ago
Solved

collect data from a column that changes position

Dear All,   Asking for your kind help. I'd like to collect values from a column (named strawberry) that is missaligned (first, it is in the 2nd column, than it is in the 3rd column and than in the ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi psvdr, another solution here:

     

    Result

     

    v1 (group transformation based on List.PositionOf)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstV9JRyixJzS0G0hmJZalAKrGoKLMsNUUpVidaKbGgIAckVlxSlFielFpUVAnkKIAxSNoEyDJDETEEsoxQRIyBLEMUEaJtzS9KzEvHYj2yRYZwESOoiDEFFhWkJhah2ofsBVMwjW67CVzEGCpqDncPLICMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    GroupedRows = Table.Group(Source, {"Column1"}, {{"All", each 
        [ a = List.Transform(Table.ToColumns(_), (x)=> List.Contains(x, "strawberry")),
          b = List.PositionOf(a, true),
          c = Table.AddColumn(_, "strawberry", (x)=> if x[Column1] = "new" then null else Record.ToList(x){b}, type text)
        ][c], type table}},
    GroupKind.Local, (s,c)=> Byte.From(c[Column1] = "new") ),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

     

     

    v2 (group transformation based on List.TransformMany)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstV9JRyixJzS0G0hmJZalAKrGoKLMsNUUpVidaKbGgIAckVlxSlFielFpUVAnkKIAxSNoEyDJDETEEsoxQRIyBLEMUEaJtzS9KzEvHYj2yRYZwESOoiDEFFhWkJhah2ofsBVMwjW67CVzEGCpqDncPLICMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Ad_GroupHelper = Table.AddColumn(AddedIndex, "GroupHelper", each if [Column1] = "new" then [Index] else null, Int64.Type),
    FilledDown = Table.FillDown(Ad_GroupHelper,{"GroupHelper"}),
    GroupedRows = Table.Group(FilledDown, {"GroupHelper"}, {{"All", each 
        [ a = Table.ToColumns(_),
          b = List.TransformMany(a,
                (y)=> {List.Contains(y, "strawberry")},
                (x,y)=> List.Select(x, (w)=> y) ),
          c = {null} & List.Skip(List.Combine(b)),
          d = Table.FromColumns(a & {c})
        ][d], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    RemovedColumns = Table.RemoveColumns(CombinedAll,{"Column5", "Column6"})
in
    RemovedColumns

     

zhqw's avatar
zhqw
Regular Visitor
2 years ago

I duplicate your data in excel like this

and transform it as a table which name is "表1"，then into power query.
the code like follow:

 let
    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"列1", type text}, {"列2", type text}, {"列3", type text}, {"列4", type text}}),
    Group = Table.Group(ChangeType,"列1",{"new",each _},
0,(x,y)=> Number.From( y ="new items have arrived")  ),
    Transform = Table.TransformColumns(Group,{"new",each
Table.InsertRows(
Table.DemoteHeaders(Table.DuplicateColumn(Table.PromoteHeaders(Table.PromoteHeaders(_)),"strawberry","strawberry-duplicated")),
0,
{[Column1="new items have arrived",Column2=null,Column3=null,Column4=null,Column5=null]}
)
}
),
    expand = Table.ExpandTableColumn(Transform, "new", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
    DeleteCol = Table.RemoveColumns(expand,{"列1"})
in
    DeleteCol
the result like this
I wish this can help you !