Forum Discussion

Gurps's avatar
Gurps
Frequent Visitor
3 years ago
Solved

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....
Need Help
Tips and Tricks
  • slorin's avatar
    slorin
    3 years ago

    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 

slorin's avatar
slorin
Super User
3 years ago

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's avatar
    Gurps
    Frequent Visitor
    3 years ago

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