Forum Discussion

SBC's avatar
SBC
Helper III
1 year ago
Solved

Need Help with Pivoting and Categorizing Data in Power BI

Hi, I'm working on a Power BI report where I need to pivot a column and categorize values based on certain conditions. However, I'm encountering some issues while pivoting and would appreciate any h...
Need Help
Tips and Tricks
  • ThxAlot's avatar
    ThxAlot
    1 year ago

    Very strightforward,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZG7CsJAEEV/JWwdYWezeZUa31pYqiGFqFgGQiz8e2fSeGcQdos5cy5cmLZ1p6F/vO9jMnepCz7EmSd+PDS38fnqh09y5kEABdelv8ACAwEDF9nwz5TfoJ+hf53c1EXlL9GPtpCAXPkr9HPbR0Ch/DX6he0joFT+Bv3S9hFQKX+LfmX7CKiVv0O/tn0EkFeBPQR4ZS8mhEglDpggW4n+HPmIiWBLTYT4zN0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductName = _t, TransactionDate = _t, ProductCategory = _t, Group = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductName", type text}, {"TransactionDate", type date}, {"ProductCategory", type text}, {"Group", Int64.Type}, {"Value", Int64.Type}}),

    #"Transformed Column Group" = Table.TransformColumns(#"Changed Type", {"Group", each if _ < 5 then Text.From(_) else ">=5"}),
    #"Pivoted Column" = Table.Pivot(#"Transformed Column Group", List.Distinct(#"Transformed Column Group"[Group]), "Group", "Value", List.Sum)
in
    #"Pivoted Column"

ZhangKun's avatar
ZhangKun
Super User
1 year ago

Add an auxiliary column to calculate whether it is greater than or equal to 5, and then pivot this auxiliary column.

let
    源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZG7CsJAEEV/JWwdYWezeZUa31pYqiGFqFgGQiz8e2fSeGcQdos5cy5cmLZ1p6F/vO9jMnepCz7EmSd+PDS38fnqh09y5kEABdelv8ACAwEDF9nwz5TfoJ+hf53c1EXlL9GPtpCAXPkr9HPbR0Ch/DX6he0joFT+Bv3S9hFQKX+LfmX7CKiVv0O/tn0EkFeBPQR4ZS8mhEglDpggW4n+HPmIiWBLTYT4zN0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductName = _t, TransactionDate = _t, ProductCategory = _t, Group = _t, Value = _t]),
    更改的类型 = Table.TransformColumnTypes(源,{{"ProductName", type text}, {"TransactionDate", type date}, {"ProductCategory", type text}, {"Group", Int64.Type}, {"Value", Int64.Type}}),
    
    已添加自定义 = Table.AddColumn(更改的类型, "自定义", each if [Group] >= 5 then "5>=" else Text.From([Group])),
    已透视列 = Table.Pivot(已添加自定义, List.Distinct(已添加自定义[自定义]), "自定义", "Value", List.Sum)
in
    已透视列