Forum Discussion
Table relationship
Hello
I have two tables:
The first table which represents the expenses incurred during a year with the structure fields (Date, Invoice Number, Amount, description, Supplier name, Heading, site) see screenshot below
The second table which represents the budgets allocated for each site by section (see screenshot below)
In this table, an annual budget is allocated for each site by section.
What is the relationship that I can create so as to have the expenditure made in relation to the budget allocated for each site with the possibility of making filters on the site, the section and the date.
Best regards
8 Replies
hello
no not yet
Hello emmanuel2021
as mussaenda is writing, the screenshots are missing. But what is also missing is what is your expected outcome. Would be useful to see some results... that on the invoices you see the related budget based on the rows existing for every site? Or to the amount of every invoice?
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHello
I have two tables:
The first table which represents the expenses incurred during a year with the structure fields (Date, Invoice Number, Amount, description, Supplier name, Heading, site) see screenshot below
The second table which represents the budgets allocated for each site by section (see screenshot below)
In this table, an annual budget is allocated for each site by section.
What is the relationship that I can create so as to have the expenditure made in relation to the budget allocated for each site with the possibility of making filters on the site, the section and the date.
Best regards
Hello emmanuel2021
you have to do 3 steps.
First you have to normalize your budget-table... meaning unpivoting all columns except the column heading. This gives you a 3-column-table with Heading, Attribute and the value.
2nd step is to add a key-column in you expense-table connecting the column heading and site. the same thing you need to do with the budget table. Add a new key-column from the columns heading and attribute.
I suppose you need a relationship in Power BI desktop and not in Power query. If you need the relationsship in power query you don't need to add the key-columns, but you can connect both table using 2 columns each. However you need to check out before, if there are all written the same (PQ is case sensitive)
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
