Table relationship

[emmanuel2021]

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

[Jimmy801]

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

[emmanuel2021]

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

[Jimmy801]

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

[emmanuel2021]

Hello,

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

[Jimmy801]

Hello @emmanuel2021 

you can normalize your budget-table as mentioned by @edhans 

I also wrote you how to connect these both tables (creating a key-column) or in PQ by connecting two columns. 

I dont't know what result are you expecting, and without you telling as whats your goal... what should we answer you?

For sure... if in your expenses table the columns heading and site are not unique, and you are connecting them to your budget table, the value of the budget will show up in several lines. But thats normal. So please let us know how your final visual or table should look like

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

[edhans]

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.

[mussaenda]

Hi @emmanuel2021 ,

We cannot see any screenshot.

[emmanuel2021]

hello 

no not yet