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 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

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"

3 Replies

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

ThxAlot

Super User

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
Super 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

Forum Discussion

Need Help with Pivoting and Categorizing Data in Power BI

Sample Data

Desired Output

Issues Encountered

3 Replies

Related content

Pivot issue

Pivot / Unpivot?

Power BI Pivot Columns ?

Power Query Pivoting columns issue

Power Query pivot