Forum Discussion
Table relationship
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
Jimmy
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
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
JimmyHello,
How to normalize my budget table at the power bi level since the source is an excel file.
The three columns will then be Heading, Budget and Site
In the Section control we will therefore have the same section several times, then in the site control we will have site several times
I see that it is possible to establish two links between the 2 tables.
how to make its links and on what cardinality?Emmanuel
To normalize a table, in Power Query right-click on the Heading field in the Budget table and select Unpivot Other Columns.
For us to really help though provide some data in a usable format, and an idea of the expected outcome.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
