Forum Discussion
How to adding text from upper row to the row below
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
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
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"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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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})
