Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I;ve a table same as shown below example.
A B C D
User Email time late
3 50 6 3
3 6 9 2
I need to accomplish following 2 tasks:
Task 1 : Create a new column as "ABC" = Sum(A2,B2,D2) - This works in excel. How to do it in Power BI. ?
Task 2 : Re-arrange or display in Visualization area above table as rows (see below) :Note : My raw data has these arranged in column wise.
A B C
User 3 3
Email 50 6
Time 9 9
Late 2 2
Hi @prady2581,
Suppose the sample table imported to Power BI looks like:
Task1
Create a calculated column using this DAX formula:
Sum = CALCULATE ( LASTNONBLANK ( 'Sum multiple columns'[User], 1 ) + LASTNONBLANK ( 'Sum multiple columns'[Email], 1 ) + LASTNONBLANK ( 'Sum multiple columns'[Time], 1 ) )
Task2
Open Query Editors, you should refer to Power Query to achieve the requirement to re-arrange columns data as rows.
let Source = Excel.Workbook(File.Contents("C:\Users\Administrator\Desktop\test.xlsx"), null, true), #"Sum multiple columns_Sheet" = Source{[Item="Sum multiple columns",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Sum multiple columns_Sheet"), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"User", Int64.Type}, {"Email", Int64.Type}, {"Time", Int64.Type}, {"Late", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Category"}}), AddRanking = (table, column, newColumn) => Table.AddIndexColumn(Table.Sort(table, {{column, Order.Descending}}), newColumn, 1, 1), #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Category"}, {{"Data", each _, type table}}), Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "Value", "Rank")}}), #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", { "Value", "Rank"}, { "Value", "Rank"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US")[Rank]), "Rank", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"1", "Value1"}, {"2", "Value2"}}) in #"Renamed Columns1"
Result output:
Best regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |