How to Combine Header data with Column data?

DAX DAX Calculations
1

Hello,

I have forms from various clubs (at least 5) that will be received into a folder in OneDrive. I want to combine the data into a file in PowerBi to create dashboards. However, I am stuck as I cannot figure out how to get the header information into a table that I can read from. Basically I want the Facility Coordinator data to become column data that I can read from. Appreciate any help/ advise with this. Thank youSample_file.xlsx (9.8 KB)

2

@hadeeh,

I’m sure the Power Query gurus on this forum can improve on this solution, but here’s what I did:

  1. after the initial Get Data, duplicated the initial table
  2. for the first table (Club Info), removed everything below row five
  3. pivoted the data so that the first column became the headers
  4. for the second table (Employee Info) created a required parameter called Club Info
  5. removed rows not related to employee data as well as blank rows
  6. added a column called Club and set that equal to the parameter captured in 4)
  7. closed and applied the changes
  8. created a relationship between the two tables based on Club

Here’s what the data model now looks like:

image
image448×568 27.2 KB

I hope this is helpful. Full solution file attached, so that you can look at the details of the applied steps in Power Query.

1 Like
3

image
image1436×1113 14.8 KB

After posting the query, I actually thought about it and came up with a similar idea. Thank you so much for your input. Much appreciated.

4

@hadeeh,

Glad I could help. If you don’t mind me asking, what’s the purpose of the bidirectional relationship and the bridge table? That structure would normally be used to address a many-to-many relationship between coordinator/clubs and employees. However you have a one-to-one relationship defined between coordinator and club, and a one-to-many between club and employee. In that case, why not just have your coordinator table filtering your employee table in a one-to-many relationship via the club fields in each table, improving performance and simplifying your model and your DAX?

  • Brian
5

Sorry for the delay in replying. Yes, you are right, it should not be bi-directional. As a rule I do not create bi-directional relationships. This was auto created by PowerBI, which I changed later, after adding more tables to the original file and came to create my relationships. Thank you