Forum Discussion

paolomint's avatar
paolomint
Helper III
5 years ago

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...
Need Help
CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

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's avatar
paolomint
Helper III
5 years ago

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's avatar
    edhans
    Community Champion
    5 years ago

    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's avatar
      paolomint
      Helper III
      5 years ago

      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