Unpivot large no of columns

[maverick21]

I have a table in Power BI in the following format

email, id, name, perm.0.email, perm.0.role, perm.0.type, perm.1.email, perm.1.role, perm.1.type,perm.2.email, perm.2.role, perm.2.type

I want to convert this table into the format

email, id, name, perm.email, perm.role, perm.type

How can I do that? The no of perm columns extends till 50 like perm.0.email.........perm.50.email etc.

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs1NzMzRUchM0VHIS8xN1VEoSC3K1TPQg4pDeUX5OQipksoCGMcQRZ0hsjpDZHVGKOqMkNUZgdUpxeoMIrfEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"email", " id", " name"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Attribute],each "perm " & Text.Range([Attribute],8),Replacer.ReplaceText,{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
    #"Replaced Value1" = Table.ReplaceValue(#"Added Index",each [Index],each Number.RoundDown([Index]/3),Replacer.ReplaceValue,{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".