How to adding text from upper row to the row below

[Anonymous]

Hallo

I am trying to make power query add the text from the cell in the row above to the text cell ind the cell rigt below. I am able to do it in Excel but i need it to be more dynamic.

I have placed af snip of how i like it to look and also the funktion used in Excel.

Colum O is how i would like the data to look like 

[wdx223_Daniel]

NewStep=#table(Table.ColumnNames(PreviousStepName)&{"NewCol"},List.Accumulate(Table.ToRows(PreviousStepName),{{},null,{}},(x,y)=>if x{1}=y{1} then {x{0}&{y&{Text.Combine(x{2}&{y{3}},",")}},y{1},x{2}&{y{3}}} else {x{0}&{y&{y{3}}},y{1},{y{3}}}){0})

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here's my solution.

1.Add an index column.

2.Add a custom column.

Text.Combine(Table.SelectRows(#"Added Index",(x)=>x[Sel]=[Sel]and x[Afd]=[Afd]and x[Index]<=[Index])[Adresse],",")

Get the result.

Here's the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc4xDsIwDAXQu2TuAjeAhQgWpi5VB0exTCDYkl0i9faUIhUlDH9535b+MLid69ZcU5bJJoWIbux+fsq9aExMQZQK3qvSI7E9VAh58/2SS+JYUnX+4fOLyW5SMLaNnwNyi4eQsbUj8J/1UFA9sCFbPQP0uSz5Poxv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sel = _t, Afd = _t, Adresse = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sel", Int64.Type}, {"Afd", Int64.Type}, {"Adresse", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Text.Combine(Table.SelectRows(#"Added Index",(x)=>x[Sel]=[Sel]and x[Afd]=[Afd]and x[Index]<=[Index])[Adresse],","))
in
    #"Added Custom"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alannavarro]

Maybe could be like this? Hope it works

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Afd"}, {{"Count", each _, type table [Sel=number, Afd=number, Indeks=number, Adresse=text]}}),
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "combine", each combine([Count])),
#"Added Custom" = (combine)=> Table.AddColumn(combine, "Custom", each Text.Combine(List.FirstN(combine[Adresse],[Indeks]))),
#"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"combine"}),
#"Expanded combine" = Table.ExpandTableColumn(#"Removed Other Columns", "combine", {"Sel", "Afd", "Indeks", "Adresse", "Custom"}, {"Sel", "Afd", "Indeks", "Adresse", "Custom"})
in
#"Expanded combine"

[Dinesh_Suranga]

@Anonymous 

Hi,

Watch following video.

https://youtu.be/Hc3d8rMSXcQ

Thank you.