Forum Discussion
How to extract each value from a range into a separate table?
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
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_InnerStéphane
3 Replies
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_InnerStéphane
That's incredible. Thank you so much for the solution.
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_InnerStéphane
