Partial pivoting

[paolomint]

Dear all,

how Can i trasform my table:

COMPANY	SHOP	YEAR	COD	VALUE
Paolo	ROME	2020	FT	100
Paolo	ROME	2020	FT	450
Paolo	ROME	2020	CO	200
Valeria	VENICE	2020	FT	80
Valeria	VENICE	2020	SE	20

in the following way in Power query?

COMPANY	SHOP	YEAR	FT	CO	SE
Paolo	ROME	2020	100
Paolo	ROME	2020	450	200
Paolo	ROME	2020
Valeria	VENICE	2020	80
Valeria	VENICE	2020			20

thanks in advance

Paolo

[paolomint]

Hi  @edhans 

here you can find the real data I'm working on in the first sheet (database), in the other sheet (result) you can see what I'd like to obtain 

https://www.dropbox.com/s/z0dry0z35pxk9vb/example_pbi.xlsx?dl=0

Here you are an extract 

DITTA	CENTRO	YEAR	COD VOCE	IMPORTO
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ON	6,01
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ON	72,2
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ON	535,07
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ONE	70
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ONE	120
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ONE	480
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ONE	650,18
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ONE	1612
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ONE	3919,59
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-ACOS	8860,16
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-ACOS	52646
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-ACOS	110937,72
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-MAN	620,43
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-MAN	2007,94
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-MAN	2948
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-MAN	5085
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-MAN	15120,75
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-MAN	76652,95

Why I'm trying to do that?

 I need to do some calculation like the following:

EBITDA = (CEN-ON) - (CEN-ONE) 

EBIT = (EBITDA) - (CENT-ACOS)

RESD = (EBIT) - (CENT-MAN)

Then I need to visualize that in this form (as matrix):

DITTA	CENTRO	YEAR	COD VOCE	IMPORTO
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ON	400
COMALI	P001 EUROSPIN - ISOLA 1	2020	CEN-ONE	150
COMALI	P001 EUROSPIN - ISOLA 1	2020	EBITDA	250
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-ACOS	20
COMALI	P001 EUROSPIN - ISOLA 1	2020	EBIT	230
COMALI	P001 EUROSPIN - ISOLA 1	2020	CENT-MAN	100
COMALI	P001 EUROSPIN - ISOLA 1	2020	RESD	130

I know that I can use directly dax formula instead of create a new table but I'm not expert in dax and using a table is easier for me. Anyhow I hope you can help me to find the best way to do that.

I hope everything is clear, if not please ask and I will reply timely

thank you very much

Paolo

[paolomint]

Hi @edhans 

sorry to bother you,

did you have a look to the data?

Everthing is clear?

Thank you very much

Paolo

[CNENFRNL]

@paolomint , easy enough

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkjMz8lX0lEK8vd1BVJGBkYGQMotBEgYGhgoxergVWJiik+Jsz+YDVESlpiTWpSZCBQJc/XzdEY3yYKQqmAIWyk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COMPANY = _t, SHOP = _t, YEAR = _t, COD = _t, VALUE = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index"),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[COD]), "COD", "VALUE"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

[paolomint]

Great @CNENFRNL , thank you very much

In the easy table I usued as example, It works.

When I try to apply it on my real model, I obtain the following error message:

It is not possible to convert null value on text type:

Detail

Type=[Type]

May I add another line to avoid this error?

thank you very much

[edhans]

@paolomint - PIVOT has limits. I think this is why it isn't on the right-click menu. It can be finnicky. That said, three questions:

1. Why is your value text? Shouln't that be a whole number, not text?
2. Why not aggregate the values? So the table below is the result?
3. Why are you pivoting in the first place? It depends on the data set, but it could be advantageous in DAX to keep these as they are.

[paolomint]

thank you @edhans 

1. the message error gets out when I try to apply the procedure in my real model

2. yes, I can aggregate 

3. About dax, It would be great but I don't know how to do. Can you kindly help me?

thanks in advance

Paolo

[edhans]

Provide some good data for us to assist with @paolomint as well as expected results. Show us where you want to end up, rather than asking how to do a specific thing. That thing (pivoting) may not be a good way to get to your destination.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.