Data Preparation

Hi anshpalash ,

Here is the whole M syntax, please check:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqsVKw1DcyBvLySnNyEJRLaVFiSWZ+npWCGYaca14KWJcJTChWJ1rJCMVAEzwGmmCaZIqmHGSgMYqB5hgGYphrgdOh5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t, L = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}, {"F", type text}, {"G", type text}, {"H", type text}, {"L", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"A"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "null")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Split([Value],":"){0}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.