cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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)

 

Sharon09_0-1605825823283.png

I need the result like below :

nameDependentsRoleSpouseChild 1Child 2
Carla Ocampo  William MartinezIsabella Martinez ParadaLuis Martinez Parada

 

Any help would be appreciated.

1 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"

View solution in original post

4 REPLIES 4
New Member

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"

 

 

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"

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors