Unpivot columns

Usinf only GUI

PART 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRMjIwMgg3sgSyglJTgKRjblJqEZB2L0pNzcNCQ1Sh8mB6nPNzc1PzSgwRTCMgUwEJQ4XNULkWCKalUqxONIoWyjHIRP/SkoLSEiuqmwvjeQd4AsngksSS0mKEb4opNh97VAFtM4SHPlKo08guI8w4Ro9YKtpmjJS+KI8gnNaY0McaU/pYY4aeHMxoZ5c5fbxkge4lC9rZBaLQUrglZbbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Week = _t, #"KPI1:Status" = _t, #"KPI2:Status" = _t, #"KPI3:Status" = _t, #"KPI4:Status" = _t, #"KPI5:Status" = _t, #"KPI6:Status" = _t, #"KPI7:Status" = _t, #"KPI8:Status" = _t, #"KPI9:Status" = _t, #"KPI1:Comments" = _t, #"KPI2:Comments" = _t, #"KPI3:Comments" = _t, #"KPI4:Comments" = _t, #"KPI5:Comments" = _t, #"KPI6:Comments" = _t, #"KPI7:Comments" = _t, #"KPI8:Comments" = _t, #"KPI9:Comments" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Week", type text}, {"KPI1:Status", type text}, {"KPI2:Status", type text}, {"KPI3:Status", type text}, {"KPI4:Status", type text}, {"KPI5:Status", type text}, {"KPI6:Status", type text}, {"KPI7:Status", type text}, {"KPI8:Status", type text}, {"KPI9:Status", type text}, {"KPI1:Comments", type text}, {"KPI2:Comments", type text}, {"KPI3:Comments", type text}, {"KPI4:Comments", type text}, {"KPI5:Comments", type text}, {"KPI6:Comments", type text}, {"KPI7:Comments", type text}, {"KPI8:Comments", type text}, {"KPI9:Comments", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type",1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Kept First Rows", {"Project Name", "Week", "KPI1:Comments", "KPI2:Comments", "KPI3:Comments", "KPI4:Comments", "KPI5:Comments", "KPI6:Comments", "KPI7:Comments", "KPI8:Comments", "KPI9:Comments"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"KPI1:Comments", "KPI2:Comments", "KPI3:Comments", "KPI4:Comments", "KPI5:Comments", "KPI6:Comments", "KPI7:Comments", "KPI8:Comments", "KPI9:Comments"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}})
in
    #"Changed Type1"

PART 2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRMjIwMgg3sgSyglJTgKRjblJqEZB2L0pNzcNCQ1Sh8mB6nPNzc1PzSgwRTCMgUwEJQ4XNULkWCKalUqxONIoWyjHIRP/SkoLSEiuqmwvjeQd4AsngksSS0mKEb4opNh97VAFtM4SHPlKo08guI8w4Ro9YKtpmjJS+KI8gnNaY0McaU/pYY4aeHMxoZ5c5fbxkge4lC9rZBaLQUrglZbbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Week = _t, #"KPI1:Status" = _t, #"KPI2:Status" = _t, #"KPI3:Status" = _t, #"KPI4:Status" = _t, #"KPI5:Status" = _t, #"KPI6:Status" = _t, #"KPI7:Status" = _t, #"KPI8:Status" = _t, #"KPI9:Status" = _t, #"KPI1:Comments" = _t, #"KPI2:Comments" = _t, #"KPI3:Comments" = _t, #"KPI4:Comments" = _t, #"KPI5:Comments" = _t, #"KPI6:Comments" = _t, #"KPI7:Comments" = _t, #"KPI8:Comments" = _t, #"KPI9:Comments" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Week", type text}, {"KPI1:Status", type text}, {"KPI2:Status", type text}, {"KPI3:Status", type text}, {"KPI4:Status", type text}, {"KPI5:Status", type text}, {"KPI6:Status", type text}, {"KPI7:Status", type text}, {"KPI8:Status", type text}, {"KPI9:Status", type text}, {"KPI1:Comments", type text}, {"KPI2:Comments", type text}, {"KPI3:Comments", type text}, {"KPI4:Comments", type text}, {"KPI5:Comments", type text}, {"KPI6:Comments", type text}, {"KPI7:Comments", type text}, {"KPI8:Comments", type text}, {"KPI9:Comments", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type",1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Kept First Rows", {"Project Name", "Week", "KPI1:Status", "KPI2:Status", "KPI3:Status", "KPI4:Status", "KPI5:Status", "KPI6:Status", "KPI7:Status", "KPI8:Status", "KPI9:Status"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"KPI1:Status", "KPI2:Status", "KPI3:Status", "KPI4:Status", "KPI5:Status", "KPI6:Status", "KPI7:Status", "KPI8:Status", "KPI9:Status"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}})
in
    #"Changed Type1"

MERGE OF PART1 AND PART 2

let
    Source = Table.NestedJoin(#"Table (3)", {"Attribute.1"}, #"Table (2)", {"Attribute.1"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Attribute.2", "Value"}, {"Table (2).Attribute.2", "Table (2).Value"})
in
    #"Expanded Table (2)"

Hi Zakaria_1980 , 

You could try this in Power Query Editor like below(powerbi-Tranform data)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRMjIwMgg3sgSyglJTgKRjblJqEZB2L0pNzcNCQ1Sh8mB6nPNzc1PzSgwRTCMgUwEJQ4XNULkWCKalUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Week = _t, #"KPI1:Status" = _t, #"KPI2:Status" = _t, #"KPI3:Status" = _t, #"KPI4:Status" = _t, #"KPI5:Status" = _t, #"KPI6:Status" = _t, #"KPI7:Status" = _t, #"KPI8:Status" = _t, #"KPI9:Status" = _t, #"KPI1:Comments" = _t, #"KPI2:Comments" = _t, #"KPI3:Comments" = _t, #"KPI4:Comments" = _t, #"KPI5:Comments" = _t, #"KPI6:Comments" = _t, #"KPI7:Comments" = _t, #"KPI8:Comments" = _t, #"KPI9:Comments" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Week", type text}, {"KPI1:Status", type text}, {"KPI2:Status", type text}, {"KPI3:Status", type text}, {"KPI4:Status", type text}, {"KPI5:Status", type text}, {"KPI6:Status", type text}, {"KPI7:Status", type text}, {"KPI8:Status", type text}, {"KPI9:Status", type text}, {"KPI1:Comments", type text}, {"KPI2:Comments", type text}, {"KPI3:Comments", type text}, {"KPI4:Comments", type text}, {"KPI5:Comments", type text}, {"KPI6:Comments", type text}, {"KPI7:Comments", type text}, {"KPI8:Comments", type text}, {"KPI9:Comments", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Week", "Project Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Project Name", "Week", "Attribute.1"}, { {"c2", each Text.Combine([Value],","), type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Grouped Rows", "c2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"c2.1", "c2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"c2.1", type text}, {"c2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"c2.1", "Status"}, {"c2.2", "Comment"}})
in
    #"Renamed Columns"

Best Regards,
Zoe Zhi

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

Zakaria_1980 

Start from your table, duplicate two times this table and take the first row from the copies:

Then in the table, let’s say “one”, select all columns KPIx:status and apply unpivot columns, then remove columns commets and split column  attribute by delimeter “:”.

Do the same for the table “two” select all columns KPIx:comments and apply unpivot columns, then remove columns status and split column attribute by delimeter “:”.

Finally merge by field attribute.1