Best way to link tables using columns in table A and values in table b

[Kmccnoodle]

Hi. 
Please help, I am going crazy. 

i have a table "spotchecks" which contains data from a form based on whether staff are meeting certain outcomes (O1-O31) when completing their work. They have 3 workflows being checked (PCP, SF, INV).
The table columns are in the the following format:

name: "staff name"

PCPO1="yes","no"

PCPO2="yes","no"

PCPO3="yes","no"

PCPO4="yes","no"

PCPO7="yes","no"

SFO4="yes","no"
SFO5="yes","no"
SFO6="yes","no"
INVO4="yes","no"
INVO6="yes","no"
INVO8="yes","no"

SFO5="yes","no"

the O# in the table above relates to the outcome values in the table below. 

i have another table "outcomes" which is in the following format:

outcome: "O1","O2","O3" etc

outcome text: "answers phone correctly" etc

i need to create a visual table that has the following columns:

outcome

outcome text

pcp

sf

inv

(with corresponding "yes", "no" answer next to the relevant outcome for pcp, sf and inv)

i have tried multiple relationships, bridging tables, measures and I cannot get it to work. 

I would appreciate any advice. 

[v-kongfanf-msft · Accepted solution]

Hi @Kmccnoodle ,

You first need to continue processing the table via the unpivot column function in the power query.

Then create the metrics using the following formula referenced below:

PCP = 
CALCULATE(
    FIRSTNONBLANK('ExtractedOutcome'[Value], 1),
    'ExtractedOutcome'[Workflow] = "PCP"
)

SF = 
CALCULATE(
    FIRSTNONBLANK('ExtractedOutcome'[Value], 1),
    'ExtractedOutcome'[Workflow] = "SF"
)

INV = 
CALCULATE(
    FIRSTNONBLANK('ExtractedOutcome'[Value], 1),
    'ExtractedOutcome'[Workflow] = "INV"
)

Best Regards,
Adamk Kong

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

[Kedar_Pande]

• Select the columns that contain outcome data (e.g., PCPO1, PCPO2, ..., INVO8) and choose Unpivot Columns.

• Create a new column 
  Outcome = Text.Middle([Attribute], 4, 2) // This extracts "O1", "O2", etc. from "PCPO1", "SFO4"

• Close & Apply
• create a relationship between the Outcome column in the reshaped Spotchecks table and the outcome column in the Outcomes table
• PCP = CALCULATE(MAX(Spotchecks[Value]), Spotchecks[Outcome] = "O1", Spotchecks[Attribute] = "PCP")
  SF = CALCULATE(MAX(Spotchecks[Value]), Spotchecks[Outcome] = "O1", Spotchecks[Attribute] = "SF")
  INV = CALCULATE(MAX(Spotchecks[Value]), Spotchecks[Outcome] = "O1", Spotchecks[Attribute] = "INV")

[123abc]

To link the two tables in Power BI and create the visual table you need, here's a step-by-step approach:

1. Unpivot the Spotchecks Table: Since the outcomes are spread across multiple columns, you should unpivot the Spotchecks table in Power Query to bring the data into a more manageable format.

   • In Power Query, select the columns for PCP, SF, and INV (all PCPO#, SFO#, INVO# columns).
   • Right-click and choose Unpivot Columns. This will give you three columns:
     • staff name
     • Attribute (this will contain "PCPO1", "SFO4", "INVO6", etc.)
     • Value (this will contain the "yes" or "no" answers)

2. Extract the Outcome from the Unpivoted Data: Once unpivoted, you'll want to extract the outcome code from the Attribute column.

   • Add a Custom Column in Power Query to extract the outcome code (the part after "PCP", "SF", or "INV"). For example, if Attribute = PCPO1, you'll want to extract O1. The formula could be something like:
     powerquery

     Copy code

     Text.End([Attribute], 2)
     This will return "O1", "O2", etc.

3. Link the Spotchecks and Outcomes Tables: Now, you have a column in the unpivoted Spotchecks table that contains outcome codes like "O1", "O2", etc. You can create a relationship between this column and the outcome column in your Outcomes table.

   • Go to the Model view in Power BI and create a one-to-many relationship between the unpivoted Outcome column from the Spotchecks table and the Outcome column in the Outcomes table.

4. Create the Visual Table: In your visual, use the Outcome and Outcome Text from the Outcomes table, and the workflow-specific answers from the unpivoted Spotchecks table.

   • You can add a Matrix visual and set it up with:
     • Outcome and Outcome Text as rows.
     • PCP, SF, INV as columns.
     • The Value (yes/no) from the Spotchecks table in the matrix values.

This approach should help you generate the desired visual by properly linking the outcomes with their respective answers for each workflow (PCP, SF, INV).