The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I would like to Summarize all my columns in order to pivot my table and have only one column per Item:
here is my initial Table:
Mapping.Column2 | col1 | col2 | col3 | col... |
A | 1 | 0 | 2 | … |
C | 2 | 3 | 0 | … |
B | 0 | 2 | 1 | … |
C | 3 | 2 | 0 | … |
A | 1 | 0 | 2 | … |
The problem is that the number of columns is different each month, so I can’t specify exactly wich column have to be summarized (And I must summarize all of them).
I already tried to code the function by using the list of names of column,
= Table.Group(Source,{"Mapping.Column2"},
{{ Text.From( each List.RemoveItems(Table.ColumnNames(Source) ,{"Mapping.Column2"})),
each List.Sum(each List.RemoveItems({Table.ColumnNames(Source) ,"Mapping.Column2"})),
type anynonnull}})
but have the Expression error:
Cannont convert Function type into Text type.
Détails :
Value=Function
Type=Type
Here is the Table want after Grouping and Pivoting:
Mapping.Column2 | A | B | C |
Col1 | 2 | 0 | 5 |
Col2 | 0 | 2 | 5 |
Col3 | 4 | 1 | 0 |
Col.. | … | … | … |
Many thanks for your help,
Chris
Solved! Go to Solution.
Hello @Anonymous
combine a Unpivot with an Pivot. Try out this solution
let
Source = #table
(
{"Mapping.Column2","col1","col2","col3"},
{ {"A","1","0","2"}, {"C","2","3","0"}, {"B","0","2","1"}, {"C","3","2","0"}, {"A","1","0","2"} }
),
ChangedType = Table.TransformColumnTypes
(
Source,
{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}}
),
UnpivotOther = Table.UnpivotOtherColumns
(
ChangedType,
{"Mapping.Column2"},
"Attribute",
"Value"
),
Pivot = Table.Pivot
(
UnpivotOther,
List.Distinct
(
UnpivotOther[Mapping.Column2]
),
"Mapping.Column2",
"Value",
List.Sum
)
in
Pivot
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Anonymous
combine a Unpivot with an Pivot. Try out this solution
let
Source = #table
(
{"Mapping.Column2","col1","col2","col3"},
{ {"A","1","0","2"}, {"C","2","3","0"}, {"B","0","2","1"}, {"C","3","2","0"}, {"A","1","0","2"} }
),
ChangedType = Table.TransformColumnTypes
(
Source,
{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}}
),
UnpivotOther = Table.UnpivotOtherColumns
(
ChangedType,
{"Mapping.Column2"},
"Attribute",
"Value"
),
Pivot = Table.Pivot
(
UnpivotOther,
List.Distinct
(
UnpivotOther[Mapping.Column2]
),
"Mapping.Column2",
"Value",
List.Sum
)
in
Pivot
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Thank You, works very well (y)