Unpivot Columns and preserving original data type.

[Guinaba]

Hi guys,

I have to unpivot 10 columns and only of them the data type is percentage, the other are numbers. However after doing it, that the final column (Value) will have only one data type. Is there a way to preserve the original format (%) or should I not include the percentage column in the unpivot process?

 

[smozgur]

I am not sure if there is a magic or better way doing this but since I don't know if any, I would probably change the percentage column to text and add a percentage sign before the unpivot action. Something like the following. The FormatAsText is the step that makes it work.

let 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY45DsMwDAS/Eqg2At5HnWcELnI36fx/IJSFGCpYkKvRzvXaLt/btp2wLQ3OFOpmwqG13mrGWYxSQxC8rcufoD1KEMxIyKz1XjPO9RaRnXwmeI9CIhhV0Wt9HISHOBFQ8ETI6LDwRArqHc/DCowFNKtEJ0RH5kwGVs695XW0lBKYEMSM2B6hWjCAeG951wxd6mJsqjkRPryKMDKm7vOpGboqmVo/QbR1/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Class = _t, Percentage = _t, Text = _t, Integer = _t, Decimal = _t]),
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Percentage", Percentage.Type}, {"Integer", Int64.Type}, {"Decimal", type number}}),
    FormatAsText = Table.TransformColumns(ChangeTypes, 
        {
            "Percentage", each Text.From(Number.Round(_ * 100, 2) ) & "%"}
        ),
    UnpivotColumns = Table.Unpivot(FormatAsText, {"Percentage", "Text", "Integer", "Decimal"}, "Attribute", "Value")
in
    UnpivotColumns

When I load it to the worksheet, it shows correctly if I would use it as report and actually can even use the percentage value as numeric.

 

[Guinaba]

Great idea @smozgur! Thank you!