Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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.
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})
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.
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"