Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
I'm wondering if I could create a table with a many-to-many relationship so it might look like this:
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |