Forum Discussion

Sharon09's avatar
Sharon09
Frequent Visitor
5 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago
    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"
Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

 

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's avatar
Anonymous
Not applicable
5 years ago
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"