Show values as column headers

[mzedda]

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

[ImkeF · Accepted solution]

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"