cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bluelinkxx New Member
New Member

Comparing budget vs actual bases on two columns

Hello, i'm trying to set the budget vs the actual in the colomn next to eachter but based on two columns.

Im new to BI and want to know if it's at least possible to make the last table in BI.

 

This is the budget:

Locationactivitybudget
AlphaA200
AlphaB100
BetaB150
BetaC100
CharlieA100

 

The actual data:

LocationactivityActual 
AlphaA220
AlphaB80
BetaB160
BetaC90
CharlieA80

 

this is how i want it. But as you see i got the problem that different locations, have the same activity's but differtent budgets.

 

 

LocationactivityActual budget
AlphaA220200
AlphaB80100
BetaB160150
BetaC90100
CharlieA80100

 

So let me know! 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Comparing budget vs actual bases on two columns

@Bluelinkxx  Please go to "Edit Queries" then it will open the Power Query Editor, where you can find the Merge Queries option to join tables.

 

image.png

 

Once you do the join, then expand the Table by clicking on the expand option available on the column header which will show options to select the fields you want from other table. In this case it is Actual

 

image.png

 

Here is the auto-generated M code for the above steps ( for your reference)

 

let
    Source = Table.NestedJoin(Test224JoinT1,{"Location", "Activity"},Test224JoinT2,{"Location", "Activity"},"Test224JoinT2",JoinKind.Inner),
    #"Expanded Test224JoinT2" = Table.ExpandTableColumn(Source, "Test224JoinT2", {"Actual "}, {"Test224JoinT2.Actual "})
in
    #"Expanded Test224JoinT2"

image.png

 

 

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





1 REPLY 1
Super User
Super User

Re: Comparing budget vs actual bases on two columns

@Bluelinkxx  Please go to "Edit Queries" then it will open the Power Query Editor, where you can find the Merge Queries option to join tables.

 

image.png

 

Once you do the join, then expand the Table by clicking on the expand option available on the column header which will show options to select the fields you want from other table. In this case it is Actual

 

image.png

 

Here is the auto-generated M code for the above steps ( for your reference)

 

let
    Source = Table.NestedJoin(Test224JoinT1,{"Location", "Activity"},Test224JoinT2,{"Location", "Activity"},"Test224JoinT2",JoinKind.Inner),
    #"Expanded Test224JoinT2" = Table.ExpandTableColumn(Source, "Test224JoinT2", {"Actual "}, {"Test224JoinT2.Actual "})
in
    #"Expanded Test224JoinT2"

image.png

 

 

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !