Need Help with Pivoting and Categorizing Data in Power BI

[SBC]

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 help or insights you can provide.

Sample Data

Here's a sample of my data:

ProductName	TransactionDate	ProductCategory	Group	Value
Product A	2024-01-01	Category X	1	12
Product B	2024-01-02	Category Y	2	3
Product C	2024-01-03	Category Z	3	4
Product D	2024-01-04	Category X	4	5
Product E	2024-01-05	Category Y	5	6
Product F	2024-01-06	Category Z	6	7
Product G	2024-01-07	Category X	7	8
Product H	2024-01-08	Category Y	8	9
Product I	2024-01-09	Category Z	9	10
Product J	2024-01-10	Category X	10	11
Product K	2024-01-11	Category Y	11	12
Product L	2024-01-12	Category Z	12	13

Desired Output

I want to pivot the 'Group' column and categorize the 'Value' column such that values less than 5 are shown under their respective group numbers, and values 5 or greater are shown under a single column labeled '5>='.

Here's how I want the output to look:

ProductName	TransactionDate	ProductCategory	1	2	3	4	5>=
Product A	2024-01-01	Category X	12
Product B	2024-01-02	Category Y		3
Product C	2024-01-03	Category Z			4
Product D	2024-01-04	Category X				5
Product E	2024-01-05	Category Y					6
Product F	2024-01-06	Category Z					7
Product G	2024-01-07	Category X					8
Product H	2024-01-08	Category Y					9
Product I	2024-01-09	Category Z					10
Product J	2024-01-10	Category X					11
Product K	2024-01-11	Category Y					12
Product L	2024-01-12	Category Z					13

Issues Encountered

While trying to pivot the 'Group' column, I'm encountering errors. 

I would appreciate any advice on how to resolve the pivoting error and correctly categorize the 'Value' column. If you have any tips or solutions, please share them. Thank you in advance for your help!

Regards,

SBC

[ThxAlot · Accepted solution]

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]

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
    已透视列

[BeaBF]

@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