Solved: Re: Unpivot columns

Zakaria_1980 · ‎07-22-2020

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 · ‎07-22-2020

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

View solution in original post

dax · ‎07-22-2020

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.

View solution in original post

Anonymous · ‎07-23-2020

@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

View solution in original post

Anonymous · ‎07-22-2020

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 · ‎07-22-2020

where should I put this coding please ?

Anonymous · ‎07-22-2020

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 · ‎07-22-2020

Spoiler

how can i attach xls file please ?

Zakaria_1980 · ‎07-22-2020

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

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 · ‎07-22-2020

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 · ‎07-23-2020

@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

Unpivot columns

Helpful resources

Power BI Monthly Update - September 2024

Microsoft Fabric & AI Learning Hackathon

Fabric Community Update - September 2024