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

Merging 3 Tables into one with related values

I'd like to merge these 3 tables:

 

datetaskIDa.HourspersonID
6/5/2018AAAA41
6/5/2018AAAA41
6/5/2018BBBB42
6/5/2018BBBB43
6/6/2018CCCC84
6/6/2018DDDD85
6/6/2018EEEEE8

6

 

datepersonIDb.Hours
6/5/201814
6/5/201814
6/5/201828
6/5/201838
6/6/201848
6/6/201858
6/6/201868

 

taskIDc.Hours
AAAA4
AAAA4
BBBB4
BBBB4
CCCC8
DDDD8
EEEEE8

 

Resulting Table

 

datepersonIDa.Hoursb.Hoursc.Hours
6/5/20181888
6/5/20182488
6/5/20183488
6/6/20184888
6/6/20185888
6/6/20186888

 

In addition to results table, when I click on a row, I'd like a seperate visual to display all associated task IDs for the date selected.  I'm sure some relationship tables need to be setup but I don't know what else to do here.  Its a tought one for me.  Any help would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Merging 3 Tables into one with related values

 

Hi @thmonte,

 

You need to setup 3 different dimension table with unique values in order to make then related with other tables. I'm using dax to make this tables but this can also be made in Query editor:

 

 

Tasks = DISTINCT(UNION(ALL(Table1[taskID]);ALL(Table3[taskID])) )
Persons = DISTINCT(UNION(ALL(Table1[personID]);ALL(Table2[personID])))
DimDate = CALENDAR("01/01/2018";"31/12/2018") 

 

To have a more complete calendar table follow this link.

 

Then you need to setup the relationships between all the tables:

 

DimDate[Date] -> Table1[Date] (one to many)

DimDate[Date] -> Table2[Date] (one to many)

Persons[personID] -> Table1[personID] (one to many)

Persons[personID] -> Table2[personID] (one to many)

Tasks[TaskID] -> Table1[taskID] (one to many) (cross filtering both sides)

Tasks[TaskID] -> Table3[taskID] (one to many)

 

This will get the expected result be aware that you should use in your final table the values from the Dimension tables (Date, Persons, Tasks) to make your visuals and then the hours from the other tables.

Tables.png

 

Be aware that in the image above the slicer is acting with the taskID table the other one is not since you have fully detail of the personID you won't be abble to show the activity for that day for all persons. In your data one person does one task per day however if in your live data you have more than one task if you select the lina in the table it will give you all the activities for that person on that day.

 

See attach PBIX file.

 

Again the way the table are calculated is one of the many ways you can do it in PBI, depending on the actual data you can have better and faster ways of doing this.

 

Regards,

MFelix

 

 



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

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
Super User
Super User

Re: Merging 3 Tables into one with related values

 

Hi @thmonte,

 

You need to setup 3 different dimension table with unique values in order to make then related with other tables. I'm using dax to make this tables but this can also be made in Query editor:

 

 

Tasks = DISTINCT(UNION(ALL(Table1[taskID]);ALL(Table3[taskID])) )
Persons = DISTINCT(UNION(ALL(Table1[personID]);ALL(Table2[personID])))
DimDate = CALENDAR("01/01/2018";"31/12/2018") 

 

To have a more complete calendar table follow this link.

 

Then you need to setup the relationships between all the tables:

 

DimDate[Date] -> Table1[Date] (one to many)

DimDate[Date] -> Table2[Date] (one to many)

Persons[personID] -> Table1[personID] (one to many)

Persons[personID] -> Table2[personID] (one to many)

Tasks[TaskID] -> Table1[taskID] (one to many) (cross filtering both sides)

Tasks[TaskID] -> Table3[taskID] (one to many)

 

This will get the expected result be aware that you should use in your final table the values from the Dimension tables (Date, Persons, Tasks) to make your visuals and then the hours from the other tables.

Tables.png

 

Be aware that in the image above the slicer is acting with the taskID table the other one is not since you have fully detail of the personID you won't be abble to show the activity for that day for all persons. In your data one person does one task per day however if in your live data you have more than one task if you select the lina in the table it will give you all the activities for that person on that day.

 

See attach PBIX file.

 

Again the way the table are calculated is one of the many ways you can do it in PBI, depending on the actual data you can have better and faster ways of doing this.

 

Regards,

MFelix

 

 



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

Proud to be a Datanaut!




View solution in original post

nirvana_moksh Established Member
Established Member

Re: Merging 3 Tables into one with related values

Load all the queries individually and then in the Power Query mode just do 'Append Queries as New' this will basically be the union of all. I think you will able to do the rest following that, as a good practice I always disable the load of the queries powering this Append in order to reduce load time.

Super User
Super User

Re: Merging 3 Tables into one with related values

Hi @nirvana_moksh,

 

Sorry for replying to this but when you do append of this tables the result will not be the expected one this will give you a single table with all the lines one after the other and the columns that aren't matching will be filled with nulls.

 

In this case you could use a merge queries that allows to make the relationship between several tables but again in this type of data this would not give the expected result because there are rows that when merged with other tables would create double or triple rows, so in the end the expected result would not be achieved.

 

Regards,

MFelix



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

Proud to be a Datanaut!




nirvana_moksh Established Member
Established Member

Re: Merging 3 Tables into one with related values

I scrolled further down to the post and realzied I skipped the last image of the 'Resulting Table' . Thanks for the reply Felix and I agree with your solution.

thmonte Member
Member

Re: Merging 3 Tables into one with related values

This worked great @MFelix Thank you for your help.  One more question.  Is it possible ot create a filter/measure to compare all 3 of the hours even tho they are not in the same data table?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 250 members 2,189 guests
Please welcome our newest community members: