Forum Discussion
Merging rows into one row to include a total
I PowerBi and an expenses spreadsheat to create a bank transfer file. I do this by combining the data from the report into one txt file that the bank can read.
The data source includes multiple rows of the same person submitting expenses during the period i.e
user1 £2
User1 £5
user2 £2
What i would like to do is merger the rows in PoweerBI that contain the same user BUT add the expenses together so the result is:
User1 £7
User2 £2
how can i automate this using transform in PowerBI without the user manually merging every 2 weeks when new data is produced?
Hi ChrisLatta -Please follow the below steps in power query editor to do:
In Power BI Desktop, go to the Home tab and click Transform Data to open the Power Query Editor.
Group the Data:Select the column containing the user names (e.g., "User").
Go to the transform tab in the Power Query Editor and click Group By.
Configure the Group By Operation:In the Group By window:
Group By: Select the column containing the user names (e.g., "User").
Operation: Choose Sum for the column containing expenses (e.g., "Amount").
Rename the aggregated column if desired (e.g., "Total Expenses").
Load the Transformed Data:Click Close & Apply to load the transformed data into Power BI.
output:
Hope this helps.
4 Replies
Hi ChrisLatta -Please follow the below steps in power query editor to do:
In Power BI Desktop, go to the Home tab and click Transform Data to open the Power Query Editor.
Group the Data:Select the column containing the user names (e.g., "User").
Go to the transform tab in the Power Query Editor and click Group By.
Configure the Group By Operation:In the Group By window:
Group By: Select the column containing the user names (e.g., "User").
Operation: Choose Sum for the column containing expenses (e.g., "Amount").
Rename the aggregated column if desired (e.g., "Total Expenses").
Load the Transformed Data:Click Close & Apply to load the transformed data into Power BI.
output:
Hope this helps.
Thank you, that worked
Hi,
Thanks for the solution rajendraongole1 offered, it is greate and i want to offer some more information for user to refer to.
hello ChrisLatta , you can create a calculated table.
Table 2 = SUMMARIZE('Table',[User],"Sum",SUM('Table'[Amount]))Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for the alternative
