How to extract each value from a range into a separate table?

[Gurps]

I have one table, let's call it "User Dimension Values"  that has the following layout:

User ID	Dimension	Dimension Values
User A	Dimension A	100..208
User B	Dimension A	100..202|204..205

This is linked with another table, let's call it "Dimension Table" which will have the following layout:

Dimension Code	Code
Dimension A	050
Dimension A	100
Dimension A	201
Dimension A	202
Dimension A	203
Dimension A	204
Dimension A	205
Dimension A	208

I want to use Power Query to create a third table inside Power Bi which would then have the following format:

User ID	Dimension	Code
User A	Dimension A	100
User A	Dimension A	201
User A	Dimension A	202
User A	Dimension A	203
User A	Dimension A	204
User A	Dimension A	205
User A	Dimension A	208
User B	Dimension A	100
User B	Dimension A	201
User B	Dimension A	202
User B	Dimension A	204
User B	Dimension A	205

So how do I take the values in the "Dimension Values" column from the "User Dimension Values" Table and use the ranges such as 

• 100..208
• 100..202|204..205

To then produce a table that will show me row by row each dimension value assigned to each user from the range?

Thanks

[slorin · Accepted solution]

More simple

let
Source = #"User Dimension Values",
List = Table.AddColumn(Source, "Dimension Values 2", each Expression.Evaluate("{" & Text.Replace([Dimension Values], "|", "} & {") & "}")),
Expand = Table.ExpandListColumn(List, "Dimension Values 2"),
Join_Inner = Table.NestedJoin(Expand, {"Dimension", "Dimension Values 2"}, #"Dimension Table", {"Dimension Code", "Code"}, "Dimension Table", JoinKind.Inner)
in
Join_Inner

Stéphane 

[Gurps]

That's incredible.  Thank you so much for the solution.  

[slorin]

Hi

Split "Dimension Values"  by "|" into rows

Convert "100..208" into list with Expression.Evaluate

Expand list

Join with "Dimension Values"  (joinkind = Inner)

let
Source = #"User Dimension Values",
Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Dimension Values", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Dimension Values"),
Convert_to_list = Table.TransformColumns(Split, {{"Dimension Values", each Expression.Evaluate("{"&_&"}")}}),
Expand = Table.ExpandListColumn(Convert_to_list, "Dimension Values"),
Join_Inner = Table.NestedJoin(Expand, {"Dimension", "Dimension Values"}, #"Dimension Table", {"Dimension Code", "Code"}, "Dimension Table", JoinKind.Inner)
in
Join_Inner

 Stéphane