New column from 2 linked columns that can appear multiple times in the table.

[RobPastoor]

I have 2 linked columns in a table, Language and Value, which can appear multiple times in the table as Language[0..n] and Value[0..n].
The number of times may differ per table.
Language[0..n] contains the language code per country (nl-NL, en-GB, de-DE, etc.), and can be placed per record in any Language[0..n] column. The corresponding Value[0..n] column contains a description.
I want to (dynamically) add a column, where if Language[0..] is equal to "nl-NL" the value of Value[0..n] is placed in the new column.
Any help is welcome...

[dufoq3 · Accepted solution]

Hi @RobPastoor, 

1. do not split your description and tables to separate posts please. You can edit your post if needed
2. do not put your description into code just send is as text

Result

v1 - Simple way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZRNj4MgEIb/i+eaVHH7cdzvQzcbs91b04PbjpFIJQG97K9faiqOgAs9EQI878zwzhwOUZLGSfoQLaKGxZ8fav0uSmBqPUP88nrdU3mq1ApN/P7Un/8wUGsp4revYV900XFxg63Q4XNVUAmIvm95Tx9o1wsCqe3brmIja42e7grZ4peMS2iR1qO4cCoAwXYM6BmEil8UTT1SNwijAuJsmg7vRA/WwqKrcYinCi6Agtw6Dm0Bg2bpDTiyxDja/tZUSmjQk5x3JRLIOS8RP+dQjrDEczFESsNSd53s3I1cp6XQOOJwneky04VzriNZwGNTbBaG++Fmu2CXWQ7V1JXX93afGI2kWWvHXZNlas2xNnbMrnyNgniajGz/K9G9PT1Qs+Xds2Q27ywJsIRvDmpYGvCZgR+SEa+sK9BpKhqWBQ03c1pZw+74Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Article number" = _t, Language1 = _t, Value1 = _t, Language2 = _t, Value2 = _t, Language3 = _t, Value3 = _t, Language4 = _t, Value4 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Article number"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({x}, {"0".."9"}), {"0".."9"})(_){0} , type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Text Before Delimiter", List.Distinct(#"Extracted Text Before Delimiter"[Attribute]), "Attribute", "Value", each _),
    #"Added Table" = Table.AddColumn(#"Pivoted Column", "Table", each Table.FromColumns({[Language], [Value]}, {"Language", "Val"}), type table),
    #"Removed Columns" = Table.RemoveColumns(#"Added Table",{"Language", "Value"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Removed Columns", "Table", {"Language", "Val"}),
    #"Added Value" = Table.AddColumn(#"Expanded Table", "Value", each if [Language] = "nl-NL" then [Val] else null, type text),
    #"Filtered Rows" = Table.SelectRows(#"Added Value", each ([Value] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Language", "Val"})
in
    #"Removed Columns1"

v2 - Elegant

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZRNj4MgEIb/i+eaVHH7cdzvQzcbs91b04PbjpFIJQG97K9faiqOgAs9EQI878zwzhwOUZLGSfoQLaKGxZ8fav0uSmBqPUP88nrdU3mq1ApN/P7Un/8wUGsp4revYV900XFxg63Q4XNVUAmIvm95Tx9o1wsCqe3brmIja42e7grZ4peMS2iR1qO4cCoAwXYM6BmEil8UTT1SNwijAuJsmg7vRA/WwqKrcYinCi6Agtw6Dm0Bg2bpDTiyxDja/tZUSmjQk5x3JRLIOS8RP+dQjrDEczFESsNSd53s3I1cp6XQOOJwneky04VzriNZwGNTbBaG++Fmu2CXWQ7V1JXX93afGI2kWWvHXZNlas2xNnbMrnyNgniajGz/K9G9PT1Qs+Xds2Q27ywJsIRvDmpYGvCZgR+SEa+sK9BpKhqWBQ03c1pZw+74Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Article number" = _t, Language1 = _t, Value1 = _t, Language2 = _t, Value2 = _t, Language3 = _t, Value3 = _t, Language4 = _t, Value4 = _t]),
    Transform = Table.FromRows(
       List.TransformMany(Table.ToRows(Source),
            each {List.PositionOf(_, "nl-NL")},
            (x,y)=> {x{0}} & List.Transform({y+1}, (a)=> x{a} ) ),
       type table[Article number=text, Value=text] )
in
    Transform

v3 - Just for fun 🙂

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZRNj4MgEIb/i+eaVHH7cdzvQzcbs91b04PbjpFIJQG97K9faiqOgAs9EQI878zwzhwOUZLGSfoQLaKGxZ8fav0uSmBqPUP88nrdU3mq1ApN/P7Un/8wUGsp4revYV900XFxg63Q4XNVUAmIvm95Tx9o1wsCqe3brmIja42e7grZ4peMS2iR1qO4cCoAwXYM6BmEil8UTT1SNwijAuJsmg7vRA/WwqKrcYinCi6Agtw6Dm0Bg2bpDTiyxDja/tZUSmjQk5x3JRLIOS8RP+dQjrDEczFESsNSd53s3I1cp6XQOOJwneky04VzriNZwGNTbBaG++Fmu2CXWQ7V1JXX93afGI2kWWvHXZNlas2xNnbMrnyNgniajGz/K9G9PT1Qs+Xds2Q27ywJsIRvDmpYGvCZgR+SEa+sK9BpKhqWBQ03c1pZw+74Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Article number" = _t, Language1 = _t, Value1 = _t, Language2 = _t, Value2 = _t, Language3 = _t, Value3 = _t, Language4 = _t, Value4 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Article number"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({x}, {"0".."9"}), {"0".."9"})(_){0} , type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Article number"}, {{"Value", each _{List.PositionOf([Value], "nl-NL")+1}[Value] , type text}})
in
    #"Grouped Rows"

[RobPastoor]

Hi dufoc3,

Quit a different approach than what I was trying.
You solution works, but in the step "Pivoted Column" de values for Language en Value got mixed up, when I try it...
But I guess I will be able to fix that.

[dufoq3]

Hi, have you tried each version of my code? Each one works with sample data. If something doesn't work - post new sample data based on your needs please.

[RobPastoor]

Hi dufoc3,

Yes, I have tested them now all 3. I like "v2 - Elegant" most.

Thanks for your help.

[dufoq3]

You're welcome. V2 is the fastest one. It could calculate milion rows in a few seconds.

[RobPastoor]

I have added 2 example tables.
The third table is the desired outcome.

[RobPastoor]

Article number	Value
12-125	Tafel
12-126	Stoel
12-127	Kast
12-128	Kruk
12-129	Kruk
12-130	Poef
12-131	Poef
12-132	Kruk
12-133	Tafel
12-134	Tafel
12-135	Kast
12-136	Stoel
12-137	Stoel
12-138	Kast
12-139	Kast
12-140	Stoel
12-141	Tafel
12-142	Stoel
12-143	Tafel
12-144	Kruk

[RobPastoor]

Article number	Language1	Value1	Language2	Value2	Language3	Value3	Language4	Value4
12-125	nl-NL	Tafel	de-DE	Tisch	en-GB	Table	fr-FR	Tableau
12-126	fr-FR	Chaise	nl-NL	Stoel	en-GB	Chair	de-DE	Stuhl
12-127	nl-NL	Kast	en-GB	Closet	fr-FR	Armoire	de-DE	Kleiderschrank
12-128	en-GB	Stool	fr-FR	Tabouret	nl-NL	Kruk	de-DE	Schemel
12-129	de-DE	Schemel	en-GB	Stool	nl-NL	Kruk	fr-FR	Tabouret
12-130	de-DE	Sitzkissen	fr-FR	Pouf	en-GB	Poof	nl-NL	Poef
12-131	en-GB	Poof	nl-NL	Poef	de-DE	Sitzkissen	fr-FR	Pouf
12-132	fr-FR	Tabouret	de-DE	Schemel	nl-NL	Kruk	en-GB	Stool
12-133	nl-NL	Tafel	en-GB	Table	de-DE	Tisch	fr-FR	Tableau
12-134	en-GB	Table	de-DE	Tisch	nl-NL	Tafel	fr-FR	Tableau
12-135	nl-NL	Kast	fr-FR	Armoire	de-DE	Kleiderschrank	en-GB	Closet
12-136	en-GB	Chair	de-DE	Stuhl	nl-NL	Stoel	fr-FR	Chaise
12-137	nl-NL	Stoel	de-DE	Stuhl	en-GB	Chair	fr-FR	Chaise
12-138	en-GB	Closet	fr-FR	Armoire	nl-NL	Kast	de-DE	Kleiderschrank
12-139	de-DE	Kleiderschrank	nl-NL	Kast	en-GB	Closet	fr-FR	Armoire
12-140	nl-NL	Stoel	en-GB	Chair	de-DE	Stuhl	fr-FR	Chaise
12-141	nl-NL	Tafel	fr-FR	Tableau	de-DE	Tisch	en-GB	Table
12-142	de-DE	Stuhl	nl-NL	Stoel	en-GB	Chair	fr-FR	Chaise
12-143	de-DE	Tisch	en-GB	Table	fr-FR	Tableau	nl-NL	Tafel
12-144	fr-FR	Tabouret	nl-NL	Kruk	en-GB	Stool	de-DE	Schemel

[RobPastoor]

Article number	Language1	Value1	Language2	Value2	Language3	Value3
12-125	nl-NL	Tafel	de-DE	Tisch	en-GB	Table
12-126	nl-NL	Stoel	en-GB	Chair	de-DE	Stuhl
12-127	nl-NL	Kast	en-GB	Closet	de-DE	Kleiderschrank
12-128	en-GB	Stool	nl-NL	Kruk	de-DE	Schemel
12-129	de-DE	Schemel	en-GB	Stool	nl-NL	Kruk
12-130	de-DE	Sitzkissen	en-GB	Poof	nl-NL	Poef
12-131	en-GB	Poof	nl-NL	Poef	de-DE	Sitzkissen
12-132	de-DE	Schemel	nl-NL	Kruk	en-GB	Stool
12-133	nl-NL	Tafel	en-GB	Table	de-DE	Tisch
12-134	en-GB	Table	de-DE	Tisch	nl-NL	Tafel
12-135	nl-NL	Kast	de-DE	Kleiderschrank	en-GB	Closet
12-136	en-GB	Chair	de-DE	Stuhl	nl-NL	Stoel
12-137	nl-NL	Stoel	de-DE	Stuhl	en-GB	Chair
12-138	en-GB	Closet	nl-NL	Kast	de-DE	Kleiderschrank
12-139	de-DE	Kleiderschrank	nl-NL	Kast	en-GB	Closet
12-140	nl-NL	Stoel	en-GB	Chair	de-DE	Stuhl
12-141	nl-NL	Tafel	de-DE	Tisch	en-GB	Table
12-142	de-DE	Stuhl	nl-NL	Stoel	en-GB	Chair
12-143	de-DE	Tisch	en-GB	Table	nl-NL	Tafel
12-144	nl-NL	Kruk	en-GB	Stool	de-DE	Schemel

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...