Forum Discussion
Relating a multiple entry column with single entry column
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
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
3 Replies
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
Wow! Thank you.
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
