Forum Discussion
Solved
Loop through a table column with condition and update the result in custom column
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.
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"
4 Replies
Any help? I need to loop through a column and put the result in custom columns one by one.
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"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"
