Split a Json Array Column

[Anonymous]

Hey Everyone,

i just get a database from MongoDb and get 2 columns, _id: we can say its the userid and the skills of that user, the problem is that skill is a array json. Is there some way to split that array(its no problem to repeat the values of _id column since i can get one skill per line)

thanks for the help

My powerbi File

[FrankAT · Accepted solution]

Hi @Anonymous ,

you can do it in Power Query like this (PBIX file attached):

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VjLbuM2FP0VQqsZwB3oYclyd7REy4wlUUPJzmTioNAT6KLddVUUCLrpH8yq+Zr8yXxJKccZW7ykIwcpihYNsjCCnKPry3PPudTtreHO7ZlV+r5nt3UznU1bt61K062NiXH768744cdmZ7id75Zd2cwas/Edu/Vq25lbtrkzJmhn/Fz+1O6M78WnNV3gFH+9//O3CXrG9n8/4NtZw07xpYzPA75JdsY4dAWejlPxeA28kuGtDC9ILn7RJqXF4++cslzBVNl+bc+mbun6pWlajdf5lefJTOGC4C3hSvxcgAb4uqqP+K/3D/s+pEsFuHQc0/FPwHXl+w7o4cdYha2tquxOsK3nwvO7ohzvjPen4Mlz2V09LLt2wZMJ39KAoJRdiwrujLuJTl2z6lRdJ2fsDxRmduCMr/AW6wRSDuVpzmVwwtKIhQvR5HEEtUxAYpwXNMgJ5sFKUcZ87tvd3HPqznREn+rW7Jq2AfIgOY1SlGecpsULnapsTadmUqcsJD9mVSQqJSjRtgwOcpX8lVigwP6MxCTTrBhLASYIp9EmxqoJUuJnMv5DSgq05Dgh14yvdTSy2nxQRp69jglIb08TME7GMgBvfK5FQTLZ/WKa9uwtZkgGA/GmLCRXWm3MJXjXw0UeHPHZKhsHtswpnN9QTGBEEhQ/fologFFIUIjDgVWLp53hBKZ15Fy+y08ptSkmU3rSdxQfhQej3g2VGaC0GqBgxnEQa9Uiw4Fykxt1DlxklGPxQKsZy4uIkwtKACLlJKRamVUSGmR5zmIajkV34OtvAykFdVjL0ph7houC8HSgy4czPCCKwzBE70KWYJp+F3K6JSl6on4/8mtZwJcTGnB2CGlta2uJBSir6Avrd6VjWVsSsywhaaGtTWYFiouoNixkLFDbghaLTbAmoxmgI5J0TVO5J2KQxy2+4h+fNl9dATIeqCbieHkBHsQ1znCwImiNl2uZRP8tgEA4XoheJh911lebQwYHHGPIxDloTU8uABj8erMQQ0MKvTxbiQL4OV7n6B3+vOGiG9/Y0EH1Wn3KtMCOn2aHLQu05wY9ftAQgfGJGItigoKYbbQGJZOANuPr0Q26+I41QF9+xxrAz96x7g93rHPrb1PC9Vcs2Y5I3cGSrVh/M84KVtAMP/7xeM90y7rE04J2Hfy8WNHeIqJ/RdtBoMVECa6curUG17q68cCzc/oJiR4kKnfqGWxPYmjA0pcThp7SQsPhTCUOmMk5Eid6RYJCEc09xdQeUsyAyW5pTmUh7N1xD+8kuCOGWs6ZzziivTmelWz71je2XvCeJQkeLOZXiXA6lfX2cNeW4MCXchKTlG4SdE0W+6PSUTmNRAUsOFoRLmZFcU770fUkPIih5/vxjfCKRM1y+S1bvc6d2+Wf38VcvMufW8YF96htfDA+395baG4kr9jHP7xqHx9dwUVvTo7p+f8KO5YBjdxhL9wp1FPyT4YbgANpLUJ5kTquvJ0Ellyiv6offO+ilWYwnWPeG4+JaUGqyek32L0BvATxluBI6F+1KSkJwDFuC/JJlRm1Vfun2Vz51Ry49epmwenpNuiU4mcqQHPPFTDT9p3Omf2HXoYeRSpzgGsRJzjQOoPyldvA/PVv3F7wNFHiG5qaIvpeeTH/22YCxM1wJNALeGBN14yHmcjdg7Dv/gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [_id = _t, skills = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"_id", type text}, {"skills", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "skills", Splitter.SplitTextByDelimiter("{", QuoteStyle.None), {"skills.1", "skills.2", "skills.3", "skills.4", "skills.5", "skills.6", "skills.7", "skills.8", "skills.9", "skills.10", "skills.11", "skills.12", "skills.13", "skills.14", "skills.15", "skills.16", "skills.17", "skills.18", "skills.19", "skills.20", "skills.21", "skills.22", "skills.23", "skills.24", "skills.25", "skills.26", "skills.27", "skills.28", "skills.29", "skills.30"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"skills.1"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"_id"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns1"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Thanks a lot, that solved my problem