extract values within cell and transpose

[PSB]

using Power query, how can I extract values within cell?

I need gNBId and cellLocalId from each {} in different column.

Also is there a max number of character per cell limit in Power Bi ?

Before

S_NodeId	S_Cell	lwNeighborRel
MNJ06941A2	ANJ06941A11	[{mnc=260, gNBIdLength=24, mcc=310, gNBId=1343232, cellLocalId=301, nRPCI=546}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1343232, cellLocalId=303, nRPCI=659}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1323406, cellLocalId=301, nRPCI=117}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1324497, cellLocalId=301, nRPCI=216}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1334485, cellLocalId=303, nRPCI=38}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1343690, cellLocalId=302, nRPCI=676}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1327593, cellLocalId=301, nRPCI=231}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1341078, cellLocalId=303, nRPCI=572}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1341078, cellLocalId=301, nRPCI=792}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1341078, cellLocalId=302, nRPCI=568}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1314404, cellLocalId=3, nRPCI=800}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1325160, cellLocalId=1, nRPCI=3}, {mnc=260, gNBIdLength=24, mcc=310, gNBId=1315783, cellLocalId=2, nRPCI=325}, {m...

After

S_NodeId	S_Cell	gNBID nRPCI
MNJ06941A2	ANJ06941A11	gNBId=1343232, nRPCI=546
MNJ06941A2	ANJ06941A11	gNBId=1329348, nRPCI=338
MNJ06941A2	ANJ06941A11	gNBId=1329348 nRPCI=363

[Greg_Deckler · Accepted solution]

@PSB Maybe:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZG7jsIwEEV/xUrtYl5+FS5YKhCg1bZRipUXsUUIDR3i30kTF5FcjKAcWzpzz9y+746nPfgkuKHOdptlQJyn/nGdSiYP1lxOX7u/w3m63P8ziTXXUjLj8pGRhYnJmnIex8Ot/I7zIwNaM/18b3fZiX9a8y6OF5x3SYcjFvDNdIhBiRNJoYkjVMqySHRNWY7a0/kEaxrV0wVlOAoucduVUZkOIcSmqwv0AVxNF9IncPV2ziurQBGQFW6BRQBlEQ79qtcqyspcLsRVp1Vy3vMcumF4AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S_NodeId = _t, S_Cell = _t, lwNeighborRel = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S_NodeId", type text}, {"S_Cell", type text}, {"lwNeighborRel", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","[","{",Replacer.ReplaceText,{"lwNeighborRel"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","}",Replacer.ReplaceText,{"lwNeighborRel"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","{","",Replacer.ReplaceText,{"lwNeighborRel"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","}}","",Replacer.ReplaceText,{"lwNeighborRel"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value3", "Custom", each Text.Split([lwNeighborRel],"},")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15", "Custom.16", "Custom.17", "Custom.18", "Custom.19", "Custom.20", "Custom.21", "Custom.22", "Custom.23", "Custom.24", "Custom.25", "Custom.26", "Custom.27", "Custom.28", "Custom.29", "Custom.30", "Custom.31", "Custom.32", "Custom.33", "Custom.34", "Custom.35", "Custom.36", "Custom.37", "Custom.38", "Custom.39", "Custom.40", "Custom.41", "Custom.42", "Custom.43", "Custom.44", "Custom.45", "Custom.46", "Custom.47", "Custom.48", "Custom.49", "Custom.50", "Custom.51", "Custom.52", "Custom.53", "Custom.54", "Custom.55", "Custom.56", "Custom.57", "Custom.58", "Custom.59", "Custom.60", "Custom.61", "Custom.62", "Custom.63", "Custom.64", "Custom.65", "Custom.66", "Custom.67", "Custom.68", "Custom.69", "Custom.70", "Custom.71", "Custom.72", "Custom.73", "Custom.74", "Custom.75", "Custom.76", "Custom.77", "Custom.78"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}, {"Custom.5", type text}, {"Custom.6", type text}, {"Custom.7", type text}, {"Custom.8", type text}, {"Custom.9", type text}, {"Custom.10", type text}, {"Custom.11", type text}, {"Custom.12", type text}, {"Custom.13", type text}, {"Custom.14", type text}, {"Custom.15", type text}, {"Custom.16", type text}, {"Custom.17", type text}, {"Custom.18", type text}, {"Custom.19", type text}, {"Custom.20", type text}, {"Custom.21", type text}, {"Custom.22", type text}, {"Custom.23", type text}, {"Custom.24", type text}, {"Custom.25", type text}, {"Custom.26", type text}, {"Custom.27", type text}, {"Custom.28", type text}, {"Custom.29", type text}, {"Custom.30", type text}, {"Custom.31", type text}, {"Custom.32", type text}, {"Custom.33", type text}, {"Custom.34", type text}, {"Custom.35", type text}, {"Custom.36", type text}, {"Custom.37", type text}, {"Custom.38", type text}, {"Custom.39", type text}, {"Custom.40", type text}, {"Custom.41", type text}, {"Custom.42", type text}, {"Custom.43", type text}, {"Custom.44", type text}, {"Custom.45", type text}, {"Custom.46", type text}, {"Custom.47", type text}, {"Custom.48", type text}, {"Custom.49", type text}, {"Custom.50", type text}, {"Custom.51", type text}, {"Custom.52", type text}, {"Custom.53", type text}, {"Custom.54", type text}, {"Custom.55", type text}, {"Custom.56", type text}, {"Custom.57", type text}, {"Custom.58", type text}, {"Custom.59", type text}, {"Custom.60", type text}, {"Custom.61", type text}, {"Custom.62", type text}, {"Custom.63", type text}, {"Custom.64", type text}, {"Custom.65", type text}, {"Custom.66", type text}, {"Custom.67", type text}, {"Custom.68", type text}, {"Custom.69", type text}, {"Custom.70", type text}, {"Custom.71", type text}, {"Custom.72", type text}, {"Custom.73", type text}, {"Custom.74", type text}, {"Custom.75", type text}, {"Custom.76", type text}, {"Custom.77", type text}, {"Custom.78", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"S_NodeId", "S_Cell", "lwNeighborRel"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"lwNeighborRel", "Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"S_NodeId", "S_Cell"}, {{"Values", each _, type table [S_NodeId=nullable text, S_Cell=nullable text, Value=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(Table.SelectColumns([Values],{"Value"}))),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values1",{"Values"})
in
    #"Removed Columns1"