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

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