Unpivot columns

[Zakaria_1980]

Dears,

i've made some changes under power query in power Bi service, and i got the following outcome. what i noticed is that for some KPIs i don't have for the same row and for the same KPI, both status and comment, but for the same KPI i have one row for status and another one for comment

Below what I want to be displayed in power Bi, so i can work on my visuals. whenever the row related to status and comments is empty, that row should be deleted

Thanks in advance.

[Anonymous · Accepted solution]

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)"

[dax · Accepted solution]

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.

[Anonymous · Accepted solution]

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)"

[Zakaria_1980]

where should I put this coding please ?

[Anonymous]

Hi @Zakaria_1980 ,

you should provide an example of the starting data structure, possibly in a form that can be easily copied, not an image.

[Zakaria_1980]

Spoiler

Spoiler

how can i attach xls file please ?

[Zakaria_1980]

Input:

Project Name

Week

KPI1:Status

KPI2:Status

KPI3:Status

KPI4:Status

KPI5:Status

KPI6:Status

KPI7:Status

KPI8:Status

KPI9:Status

KPI1:Comments

KPI2:Comments

KPI3:Comments

KPI4:Comments

KPI5:Comments

KPI6:Comments

KPI7:Comments

KPI8:Comments

KPI9:Comments

Project 1

2020W29

Red

Amber

Green

Green

Green

Red

Green

Red

Amber

Comment1

Comment2

Comment6

Comment8

Comment9

Output:

KPI

Status

Comments

Project 1

2020W29

KPI1

Red

Comment1

Project 1

2020W29

KPI2

Amber

Comment2

Project 1

2020W29

KPI3

Green

Project 1

2020W29

KPI4

Green

Project 1

2020W29

KPI5

Green

Project 1

2020W29

KPI6

Red

Comment6

Project 1

2020W29

KPI7

Green

Project 1

2020W29

KPI8

Red

Comment8

Project 1

2020W29

KPI9

Amber

Comment9

[dax · Accepted solution]

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.

[Anonymous · Accepted solution]

@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