Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Zakaria_1980
Post Patron
Post Patron

Unpivot columns

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

Capture.PNG

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

 

Capture1.PNG

 

Thanks in advance.

 

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

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"

790.PNG

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
Not applicable

@Zakaria_1980 

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

 

image.png

 

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

image.png

 

 

 

 

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

 

 

 

where should I put this coding please ?

 

Anonymous
Not applicable

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.
 

Spoiler
Spoiler
 

how can i attach xls file please ?

 

Input:                   
                    
Project NameWeekKPI1:StatusKPI2:StatusKPI3:StatusKPI4:StatusKPI5:StatusKPI6:StatusKPI7:StatusKPI8:StatusKPI9:StatusKPI1:CommentsKPI2:CommentsKPI3:CommentsKPI4:CommentsKPI5:CommentsKPI6:CommentsKPI7:CommentsKPI8:CommentsKPI9:Comments
Project 12020W29RedAmberGreenGreenGreenRedGreenRedAmberComment1Comment2   Comment6 Comment8Comment9
                    
Output:                   
  KPIStatusComments               
Project 12020W29KPI1RedComment1               
Project 12020W29KPI2AmberComment2               
Project 12020W29KPI3Green                
Project 12020W29KPI4Green                
Project 12020W29KPI5Green                
Project 12020W29KPI6RedComment6               
Project 12020W29KPI7Green                
Project 12020W29KPI8RedComment8               
Project 12020W29KPI9AmberComment9               
                    

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"

790.PNG

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
Not applicable

@Zakaria_1980 

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

 

image.png

 

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

image.png

 

 

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors