Forum Discussion
Partial pivoting
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
7 Replies
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"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
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:
- Why is your value text? Shouln't that be a whole number, not text?
- Why not aggregate the values? So the table below is the result?
- 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.
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
