Not able to Parse the JSON column in correct table format

[Anonymous]

Hi Team,

I have column "Description" in which value is in below format :- 

[{"0": "Shubham"}, {"1": "Tech Lead"}]

When i right click on the column and go to parse JSON option, it creates 2 rows and 2 columns

0	1
Shubham	null
null	Tech Lead

But instead i wanted it in below format, 

0	1
Shubham	Tech Lead

I dont want null values to show up. 

Could you please help me on this ?

Thanks,

Shubham

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use following query formula if they meet your requirement:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiq6OUTKIUbJSiFEKzihNykjMjVGq1VEAihpCRENSkzMUfFITU4DisUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromList(Text.Split(Text.Combine(List.ReplaceMatchingItems(Text.ToList([Text]),{{"{",""},{"}",""},{"[",""},{"]",""},{" ",""},{"""",""}})),","))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Text"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1"}, {"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"C1", "C2"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"0", type text}, {"1", type text}})
in
    #"Changed Type1"

Regards,

Xiaoxin Sheng