Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Location | activity | budget |
Alpha | A | 200 |
Alpha | B | 100 |
Beta | B | 150 |
Beta | C | 100 |
Charlie | A | 100 |
The actual data:
Location | activity | Actual |
Alpha | A | 220 |
Alpha | B | 80 |
Beta | B | 160 |
Beta | C | 90 |
Charlie | A | 80 |
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.
Location | activity | Actual | budget |
Alpha | A | 220 | 200 |
Alpha | B | 80 | 100 |
Beta | B | 160 | 150 |
Beta | C | 90 | 100 |
Charlie | A | 80 | 100 |
So let me know!
Solved! Go to Solution.
@Anonymous Please go to "Edit Queries" then it will open the Power Query Editor, where you can find the Merge Queries option to join tables.
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
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"
Proud to be a PBI Community Champion
@Anonymous Please go to "Edit Queries" then it will open the Power Query Editor, where you can find the Merge Queries option to join tables.
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
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"
Proud to be a PBI Community Champion
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |