Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.