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 F...
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.
