cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Connect multiple columns in two tables

I have two tables that pull project information. The first table is pulled from our project database and includes all info with deadlines for all of the project steps, the project owner, etc.

 

The second table has calculated columns that count how many times a certain project step is due for a certain period across all projects. So each project step column looks to the left of the timeline table (which has two years of period + year), then scans the matching project step column in the project info table and counts how many times that period + year appears. This is perfect for displaying a timeline that shows how many projects are due for each project step across the years (i.e. distribution across time of work). 

 

However, I can't connect the multiple project step columns to the project info table. It will only let me connect one column at a time, so my graphs are not dynamic in the report. I have 4 different project steps that I'd like to show on the timeline that can change dynamically when, for example, a specific project owner is chosen.

 

Here's an example of my project info table:

Project NameOwnerStep 1 Due DateStep 2 Due DateStep 3 Due DateStep 4 Due Date
ABCJack2020P062020P072020P082020P10
DEFSally2020P012020P022020P062020P08
GHIBob2020P022020P052020P062020P010
JKLSally2020P032020P062020P072020P08
MNOBob2020P012020P052020P062020P09

 

Here's an example of my timeline table ("calculated" from data above):

TimelineStep 1 CountStep 2 CountStep 3 CountStep 4 Count
2020P06113 
2020P07 11 
2020P08 1 2

 

Not every date shown in timeline appears in the project info table. Right now I can only create one active relationship at a time with the Timeline column and one Step Due Date column, not all four Step Due Date columns like I need. This means that charts using the Timeline table info don't change when I select a project name or an owner.

2 REPLIES 2
Community Support
Community Support

Hi @sfink22 ,

 

We can use the unpivot function in Power Query Editor to meet your requirement.

 

1. We can select [Project Name] and [Owner] in Power Query Editor.

 

connect 1.jpg

 

2. Then use the unpivot other columns,

 

connect 2.jpg

 

We can get a table like this,

 

connect 3.jpg

 

4. And we need to create a relationship between two tables based on Timeline.

 

connect 4.jpg

 

5. And we can create a matrix table on report page. Put the [Timeline] to Rows, put the [Attribute] to Columns, put the [value] to values and count it.

We need to configure the [Timeline] is not blank.

 

connect 5.jpg

 

6. At last we can create two slicers to control the matrix table.

 

connect 6.jpg

 

connect 7.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support
Community Support

Hi @sfink22 ,

 

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors