Loop through a table column with condition and update the result in custom column

[Sharon09]

Hi All,

I'm using Connection and Contact entity of Dynamics 365 online. As shown in the screenshot I have Primary contact in the "name" column and Dependents in the secound column. Based on the Role id I need to populate the spouse(custom column) and Child1(custom column) and Child 2 (custom column)

I need the result like below :

name	Dependents	Role	Spouse	Child 1	Child 2
Carla Ocampo			William Martinez	Isabella Martinez Parada	Luis Martinez Parada

Any help would be appreciated.

[Sharon09]

Any help? I need to loop through a column and put the result in custom columns one by one.

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRKgYRSrE6MD4QpSUaogsYgQWSkpJAOpJgOiB8kIIkQ3QBI3QBY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, sposa = _t, figli = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"name", type text}, {"sposa", type text}, {"figli", type text}}),
    #"Sostituito valore" = Table.ReplaceValue(#"Modificato tipo","",null,Replacer.ReplaceValue,{"sposa"}),
    #"Ricopiato in basso" = Table.FillDown(#"Sostituito valore",{"sposa"}),
    #"Raggruppate righe" = Table.Group(#"Ricopiato in basso", {"name", "sposa"}, {{"figlio", each _[figli] }}),
    #"Valori estratti" = Table.TransformColumns(#"Raggruppate righe", {"figlio", each Text.Combine(List.Transform(List.Skip(_), Text.From), ","), type text}),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Valori estratti", "figlio", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"figlio.1", "figlio.2", "figlio.3"}),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"figlio.1", type text}, {"figlio.2", type text}, {"figlio.3", type text}})
in
    #"Modificato tipo1"

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRKgYRSrE6MD4QpSUaogsYgQWSkpJAOpJgOiB8kIIkQ3QBI3QBY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, sposa = _t, figli = _t]),
    #"Raggruppate righe" = Table.Group(Origine, {"name", "sposa"}, {{"figlio", each Text.Combine( List.Skip(_[figli]),",") }}, GroupKind.Local, (x,y)=>Number.From(y[sposa]<>"")),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Raggruppate righe", "figlio", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"figlio.1", "figlio.2", "figlio.3"})
in
    #"Suddividi colonna in base al delimitatore"

[Anonymous · Accepted solution]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRKgYRSrE6MD4QpSUaogsYgQWSkpJAOpJgOiB8kIIkQ3QBI3QBY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, sposa = _t, figli = _t]),
    #"Raggruppate righe" = Table.Group(Origine, {"name", "sposa"}, {{"figlio", each  Text.Combine(List.Skip(_[figli]),",") }}, GroupKind.Local, (x,y)=>Number.From(y[sposa]<>"")),
prolemax=List.Max(List.Transform(#"Raggruppate righe"[figlio], each List.Count(Text.Split(_,",")))),
 cols=List.Transform({1..prolemax}, each "figlio."&Text.From(_)),

    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Raggruppate righe", "figlio", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), cols)
in
    #"Suddividi colonna in base al delimitatore"