Forum Discussion
Solved
Show values as column headers
Hi all,
I need to explode the values as column headers. For example, I have this situation:
| tabella | campo | risultato | pilastro | indicatore |
| x | x | 1 | adeguatezza | data quality |
| x | x | 0,5 | copertura | data quality |
| x | y | 1 | adeguatezza | data dictionary |
| z | z | 1 | adeguatezza | business glossary |
| z | z | 1 | copertura | business glossary |
| z | z | 1 | copertura | data quality |
This is what I want in Power Bi:
| tabella | campo | data quality | data dictionary | business glossary | adeguatezza | copertura |
| x | x | 0,75 | 1 | 0,5 | ||
| x | y | 1 | 1 | |||
| z | z | 1 | 1 | 1 | 1 |
Can anyone help me to solve the problem?
Thanks
Hi mzedda ,
I would split this table up into 2, then append befor pivoting with an average aggregation:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSAWNDIE5MSU0vTSxJrapKBPJSEksSFQpLE3MySyqVYnUQSg10TIFkcn5BalFJaRFOpZW4TU3JTC7JzM9LLIKorgKKV2FVnVRanJmXWlyskJ6TX1yMTT2yM0hTjeroWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tabella = _t, campo = _t, risultato = _t, pilastro = _t, indicatore = _t]), _staging = Table.TransformColumnTypes(Source,{{"tabella", type text}, {"campo", type text}, {"risultato", type number}, {"pilastro", type text}, {"indicatore", type text}}, "de-DE"), #"Removed Columns" = Table.RemoveColumns(_staging,{"pilastro"}), _tbl1 = Table.RenameColumns(#"Removed Columns",{{"indicatore", "Header"}}), Custom1 = _staging, #"Removed Columns1" = Table.RemoveColumns(Custom1,{"indicatore"}), _tbl2 = Table.RenameColumns(#"Removed Columns1",{{"pilastro", "Header"}}), Custom2 = _tbl2 & _tbl1, #"Pivoted Column" = Table.Pivot(Custom2, List.Distinct(Custom2[Header]), "Header", "risultato", List.Average) in #"Pivoted Column"
1 Reply
Hi mzedda ,
I would split this table up into 2, then append befor pivoting with an average aggregation:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSAWNDIE5MSU0vTSxJrapKBPJSEksSFQpLE3MySyqVYnUQSg10TIFkcn5BalFJaRFOpZW4TU3JTC7JzM9LLIKorgKKV2FVnVRanJmXWlyskJ6TX1yMTT2yM0hTjeroWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tabella = _t, campo = _t, risultato = _t, pilastro = _t, indicatore = _t]), _staging = Table.TransformColumnTypes(Source,{{"tabella", type text}, {"campo", type text}, {"risultato", type number}, {"pilastro", type text}, {"indicatore", type text}}, "de-DE"), #"Removed Columns" = Table.RemoveColumns(_staging,{"pilastro"}), _tbl1 = Table.RenameColumns(#"Removed Columns",{{"indicatore", "Header"}}), Custom1 = _staging, #"Removed Columns1" = Table.RemoveColumns(Custom1,{"indicatore"}), _tbl2 = Table.RenameColumns(#"Removed Columns1",{{"pilastro", "Header"}}), Custom2 = _tbl2 & _tbl1, #"Pivoted Column" = Table.Pivot(Custom2, List.Distinct(Custom2[Header]), "Header", "risultato", List.Average) in #"Pivoted Column"