Relating a multiple entry column with single entry column

[bradc]

Hi All,

I have a column relationship question between tables.  I basically have 2 tables.  Table (1) containers data with a field containing multiple codes.

Table (2) contains the individual codes and their descriptions

Can anyone assist with a method of relating the 2 tables so that I can produce a graph of the codes, counting the number of entries in Table (1)?

All help much appreciated

B

[sevenhills]

https://www.youtube.com/watch?v=Lb73w8ZjZTw

Optional: 

If you want to keep the data in original table as is, then in such case duplicate the table.

This video explains a split column into multiple rows. And, once you have the data ready for table 1, join to table 2 i.e., start building the relationships. Later do your DAX and visualizations per your needs.

Thanks

[PhilipTreacy · Accepted solution]

@bradc 

Download PBIX file with the example shown below

You need to split the Codes column in the first table into separate rows for each code.  Do this by splitting the column by delimiter (space) in Power Query, choosing to create rows rather than the default columns

You can then have a 1 to many relationship between table2 and table1 on the Code field, this will allow you to associate the codes in Table1 with the descriptions in Table2 - but that isn't required if you just want to count entries from table1?

If this isn't what you are after, please show an example of the desired result.

Regards

Phil

[bradc]

Wow!  Thank you.