Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
psvdr
Frequent Visitor

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 4th column) in the dataset.

Input data is in the first 4 columns on the left hand side, expected output in the last column in the example table below.

It's always the strawberry, and there's always a header (=new items have arrived) before any alignment change.

newitemshavearrived   
applestrawberry   strawberry
46    6
12    2
31    1
newitemshavearrived   
appleorangestrawberry   
121   1
223   3
newitemshavearrived   
appleorangepearstrawberry  
3151  1
1214  4
1327  7
2463  3

 

As there are hundreds of thousands of rows, power query is the preferred way.

How can I achieve this?

Thanks in advance.

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @psvdr, another solution here:

 

Result

dufoq3_0-1717137524656.png

 

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

 


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

6 REPLIES 6
slorin
Super User
Super User

Hi @psvdr , @zhqw , @dufoq3 

Another solution without Table.Group

 

One item

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]),
PositionOf = Table.AddColumn(Source, "strawberry",
each List.PositionOf(Record.ToList(_), "strawberry")),
Replace_Position = Table.ReplaceValue(PositionOf, -1, null, Replacer.ReplaceValue, {"strawberry"}),
FillDown = Table.FillDown(Replace_Position, {"strawberry"}),
Replace_Value = Table.ReplaceValue(FillDown, each Record.ToList(_),
each null, (p,r,z)=> try Number.From(r{p}) otherwise null, {"strawberry"})
in
Replace_Value

 

Many items

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]),
My_List = List.Buffer({"strawberry","pear","apple","orange"}),
PositionOf = Table.AddColumn(Source, "My_List",
(x) => List.ReplaceValue(List.Transform(My_List,
each List.PositionOf(Record.ToList(x), _)), -1, null, Replacer.ReplaceValue)),
Replace_Position = Table.ReplaceValue(PositionOf, each List.NonNullCount([My_List]), null,
(x,y,z) => if y>0 then x else z, {"My_List"}),
FillDown = Table.FillDown(Replace_Position, {"My_List"}),
Replace_Value = Table.ReplaceValue(FillDown, each Record.ToList(_), each null,
(p,r,z)=> Table.FromRows({List.Transform(p ?? List.Repeat({null},List.Count(My_List)),
each try Number.From(r{_}) otherwise null)}, My_List), {"My_List"}),
Expand = Table.ExpandTableColumn(Replace_Value, "My_List", My_List, My_List)
in
Expand

Stéphane

psvdr
Frequent Visitor

Dear @zhqw & @dufoq3 ,

Thank you so much for your efforts. Much appreciated.

Please give me some time to digest.

dufoq3
Super User
Super User

Hi @psvdr, another solution here:

 

Result

dufoq3_0-1717137524656.png

 

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

 


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

zhqw
Regular Visitor

so excellent solution!

zhqw
Regular Visitor

I duplicate your data in excel like this

source data.pngand 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
result.png
I wish this can help you !
 

 

v-xinruzhu-msft
Community Support
Community Support

Hi @psvdr 

I have some questions that

1.Is the data you have offered are in the same table? or they represent 3 tables?

2.Is the column name 'strawbeyy' or the  strawbeyy just a value in a column?

 

Best Regards!

Yolo Zhu

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors