Forum Discussion

Rakesk13

Advocate II

3 months ago

Solved

How to add two different age bucket from two different table

Hi team,

i have two different table with two different age bucket like

Table 1:

Age ID

0-5 10

5-10 20

50-60 5

Table 2:

Age ID

0-5 30

5-10 60

11-20 70

OUTPUT:

Age ID

0-5 40

5-10 80

11-20 70

50-60 5

looking for this type of output,please can you help on this, really appreciated for quick help.

Thanks & Regards,

Rakesh Jadhav

Need Help

123abc
3 months ago
Step 1:
plese follow the link, i have solved the issue and save it in pbix file please download the pbix file:

https://drive.google.com/file/d/1HqEY4DvldfpwOJ93Tlp1DHVpKSFuw58I/view?usp=sharing

Go to power queries editors:
Just to to home > append queries > append queries as New
for append queries both tables name should be same, other wise tables not combine.

> select table 1 and table 2
> after append, use Group By IN Transform tab, extreme letf side click on "Group BY"
> Group by: Age
> New column : ID
> Operation: Sum
> Column: ID
Out put wil be as you desired ... DOWNLOAD THE PBIX FILE IN GIVEN LINK.

I hope this helps!
If you found this answer helpful:
Mark it as the solution to help others find it faster.
Give it a kudo to show your appreciation!
Thank you for being an awesome community member!
123abc
3 months ago
Step: 2
After step 1: you have one more issue The sequence of your buckets:
The sequence of buckets: so follow the following steps and use following query formula:
In power query editor click on Add Column > Click on custome column > In New column name.. give name > In cumtome column formula add given below formual > click on OK, and run the query.

if [Age] = "0-5" then 1
else if [Age] = "5-10" then 2
else if [Age] = "11-20" then 3
else if [Age] = "50-60" then 4
else null

Then click on Table View:
click Sr. Number column
click Column Tools
click Sort by Column
click Click Sr. Number
Go back to Report view.

I hope this helps!
If you found this answer helpful:
Mark it as the solution to help others find it faster.
Give it a kudo to show your appreciation!
Thank you for being an awesome community member!

6 Replies

v-moharafi-msft
Community Support
3 months ago
Hi Rakesk13 ,

Thank you for reaching out to Microsoft Fabric Community and Thanks to Kedar_Pande , 123abc and ERD for Sharing valuable insights.

Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Best Regards,

Abdul Rafi.
v-moharafi-msft
Community Support
3 months ago
Hi Rakesk13 ,

Could you please confirm if the issue has been resolved? If not, feel free to reach out if you have any further questions.

Your update would be helpful for other members who may face a similar issue.

Best Regards,

Abdul Rafi
123abc
Community Champion
3 months ago
Step 1:
plese follow the link, i have solved the issue and save it in pbix file please download the pbix file:

https://drive.google.com/file/d/1HqEY4DvldfpwOJ93Tlp1DHVpKSFuw58I/view?usp=sharing

Go to power queries editors:
Just to to home > append queries > append queries as New
for append queries both tables name should be same, other wise tables not combine.

> select table 1 and table 2
> after append, use Group By IN Transform tab, extreme letf side click on "Group BY"
> Group by: Age
> New column : ID
> Operation: Sum
> Column: ID
Out put wil be as you desired ... DOWNLOAD THE PBIX FILE IN GIVEN LINK.

I hope this helps!
If you found this answer helpful:
Mark it as the solution to help others find it faster.
Give it a kudo to show your appreciation!
Thank you for being an awesome community member!

ERD

Community Champion

3 months ago

Hi Rakesk13,

You can do this is Power Query by Appending tables:

let
    Source = Table.Combine({#"Table 1", #"Table 2"}),
    #"Grouped Rows" = Table.Group(Source, {"Age "}, {{"Id", each List.Sum([#" ID"]), type nullable number}})
in
    #"Grouped Rows"

123abc
Community Champion
3 months ago
Step: 2
After step 1: you have one more issue The sequence of your buckets:
The sequence of buckets: so follow the following steps and use following query formula:
In power query editor click on Add Column > Click on custome column > In New column name.. give name > In cumtome column formula add given below formual > click on OK, and run the query.

if [Age] = "0-5" then 1
else if [Age] = "5-10" then 2
else if [Age] = "11-20" then 3
else if [Age] = "50-60" then 4
else null

Then click on Table View:
click Sr. Number column
click Column Tools
click Sort by Column
click Click Sr. Number
Go back to Report view.

I hope this helps!
If you found this answer helpful:
Mark it as the solution to help others find it faster.
Give it a kudo to show your appreciation!
Thank you for being an awesome community member!
Kedar_Pande
Super User
3 months ago

Do this in Power Query. Append Table 2 to Table 1, then group by Age and sum the ID column.

Home > Append Queries, add Table 2 to Table 1.

Select the Age column, Group By, sum ID.

Forum Discussion

How to add two different age bucket from two different table

6 Replies

Related content

Different schema into different destination

Page Level filter for different groups

Difference between different Staging lakehouses

Dynamic Calculation Different Period

How to add two different calculated columns of two different table