Forum Discussion

SBC's avatar
SBC
Icon for Helper III rankHelper 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"

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
1 year ago

SBC Hi! try with:

 

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}}),

// Step 1: Create a New Group Column for Pivoting
CategorizedTable = Table.AddColumn(#"Changed Type", "GroupCategory", each if [Group] >= 5 then "5>=" else Text.From([Group])),

// Step 2: Remove the 'Group' Column Since It's No Longer Needed
RemovedGroupColumn = Table.RemoveColumns(CategorizedTable, {"Group"}),

// Step 3: Pivot Table Using GroupCategory
PivotedTable = Table.Pivot(
RemovedGroupColumn,
List.Distinct(RemovedGroupColumn[GroupCategory]), // Ensure all columns are included
"GroupCategory",
"Value",
List.Sum
),

// Step 4: Ensure All Expected Columns Exist (Even if Missing in Data)
FinalTable = Table.SelectColumns(
PivotedTable,
{"ProductName", "TransactionDate", "ProductCategory", "1", "2", "3", "4", "5>="},
MissingField.UseNull // This ensures missing columns are still included
)
in
FinalTable

 

BBF