Extracting a field with multiple values to a new table.

[dparkinson]

This is related to my previous question and I'm wondering if there is a better way to do things.

Basically I have a database that stores data with muliple values in the same field, so for example, one record in the database will have multiple categories associated to it.  There is a lookup table, but I'm not sure how best to return the information in a usable way.

I'm thinking that it might be best to extract the values to a separate table, that way I can use the values for cross filtering / slicers etc.

Example tables:

Example Orders TableLookup Table

I'm wondering if I could create a table with a many-to-many relationship so it might look like this:

Example OrderCategories TableExample Relationship

My questions are:

- do you think this would be overall beneficial?

- how do I create the new OrderCategories table from the existing Orders and Lookup tables?

Many thanks for any assistance.

Kind regards,

D.

[v-ljerr-msft · Accepted solution]

Hi @dparkinson,

Yes, creating the OrderCategories table seems a better solution here.

And you can use the Split Column by Delimiter to Rows option in Query Editor to create the OrderCategories table.

1. Duplicate Order/Things table in Query Editor.

2. Right click on the Categories/Thing Companies column, and choose Split Column by Delimiter.

3. Select "Comma", "Split into Rows" on the popup tab.

4. Filter out Blank rows.

5. Click Close&Apply.

Final relationship.

Here is the modifed pbix file for your reference. 

Regards