Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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)"
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.
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
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 ?
Hi @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 | |||||||||||||||
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.
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