Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RobPastoor
Regular Visitor

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

‎03-08-2024 12:39 PM 
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...
Labels:
Message 1 of 11
467 Views
0
1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

‎03-09-2024 07:11 AM

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

dufoq3_0-1709997038735.png

 

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"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Message 7 of 11
393 Views
0
10 REPLIES 10
dufoq3
Super User
Super User

‎03-09-2024 07:11 AM

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

dufoq3_0-1709997038735.png

 

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"

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 7 of 11
394 Views
0
RobPastoor
Regular Visitor
In response to dufoq3

‎03-09-2024 07:34 AM

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.

Message 8 of 11
387 Views
0
dufoq3
Super User
Super User
In response to RobPastoor

‎03-09-2024 07:53 AM

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 9 of 11
380 Views
0
RobPastoor
Regular Visitor
In response to dufoq3

‎03-10-2024 03:51 AM

Hi dufoc3,

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

Thanks for your help.

Message 10 of 11
346 Views
0
dufoq3
Super User
Super User
In response to RobPastoor

‎03-10-2024 03:58 AM

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 11 of 11
330 Views
0
RobPastoor
Regular Visitor

‎03-09-2024 06:19 AM 
I have added 2 example tables.
The third table is the desired outcome.
Message 6 of 11
403 Views
0
RobPastoor
Regular Visitor

‎03-09-2024 06:17 AM
Article numberValue
12-125Tafel
12-126Stoel
12-127Kast
12-128Kruk
12-129Kruk
12-130Poef
12-131Poef
12-132Kruk
12-133Tafel
12-134Tafel
12-135Kast
12-136Stoel
12-137Stoel
12-138Kast
12-139Kast
12-140Stoel
12-141Tafel
12-142Stoel
12-143Tafel
12-144Kruk
Message 5 of 11
405 Views
0
RobPastoor
Regular Visitor

‎03-09-2024 06:16 AM
Article numberLanguage1Value1Language2Value2Language3Value3Language4Value4
12-125nl-NLTafelde-DETischen-GBTablefr-FRTableau
12-126fr-FRChaisenl-NLStoelen-GBChairde-DEStuhl
12-127nl-NLKasten-GBClosetfr-FRArmoirede-DEKleiderschrank
12-128en-GBStoolfr-FRTabouretnl-NLKrukde-DESchemel
12-129de-DESchemelen-GBStoolnl-NLKrukfr-FRTabouret
12-130de-DESitzkissenfr-FRPoufen-GBPoofnl-NLPoef
12-131en-GBPoofnl-NLPoefde-DESitzkissenfr-FRPouf
12-132fr-FRTabouretde-DESchemelnl-NLKruken-GBStool
12-133nl-NLTafelen-GBTablede-DETischfr-FRTableau
12-134en-GBTablede-DETischnl-NLTafelfr-FRTableau
12-135nl-NLKastfr-FRArmoirede-DEKleiderschranken-GBCloset
12-136en-GBChairde-DEStuhlnl-NLStoelfr-FRChaise
12-137nl-NLStoelde-DEStuhlen-GBChairfr-FRChaise
12-138en-GBClosetfr-FRArmoirenl-NLKastde-DEKleiderschrank
12-139de-DEKleiderschranknl-NLKasten-GBClosetfr-FRArmoire
12-140nl-NLStoelen-GBChairde-DEStuhlfr-FRChaise
12-141nl-NLTafelfr-FRTableaude-DETischen-GBTable
12-142de-DEStuhlnl-NLStoelen-GBChairfr-FRChaise
12-143de-DETischen-GBTablefr-FRTableaunl-NLTafel
12-144fr-FRTabouretnl-NLKruken-GBStoolde-DESchemel
Message 4 of 11
407 Views
0
RobPastoor
Regular Visitor

‎03-09-2024 06:14 AM
Article numberLanguage1Value1Language2Value2Language3Value3
12-125nl-NLTafelde-DETischen-GBTable
12-126nl-NLStoelen-GBChairde-DEStuhl
12-127nl-NLKasten-GBClosetde-DEKleiderschrank
12-128en-GBStoolnl-NLKrukde-DESchemel
12-129de-DESchemelen-GBStoolnl-NLKruk
12-130de-DESitzkissenen-GBPoofnl-NLPoef
12-131en-GBPoofnl-NLPoefde-DESitzkissen
12-132de-DESchemelnl-NLKruken-GBStool
12-133nl-NLTafelen-GBTablede-DETisch
12-134en-GBTablede-DETischnl-NLTafel
12-135nl-NLKastde-DEKleiderschranken-GBCloset
12-136en-GBChairde-DEStuhlnl-NLStoel
12-137nl-NLStoelde-DEStuhlen-GBChair
12-138en-GBClosetnl-NLKastde-DEKleiderschrank
12-139de-DEKleiderschranknl-NLKasten-GBCloset
12-140nl-NLStoelen-GBChairde-DEStuhl
12-141nl-NLTafelde-DETischen-GBTable
12-142de-DEStuhlnl-NLStoelen-GBChair
12-143de-DETischen-GBTablenl-NLTafel
12-144nl-NLKruken-GBStoolde-DESchemel
Message 3 of 11
409 Views
0
lbendlin
Super User
Super User

‎03-08-2024 07:18 PM

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...

Message 2 of 11
427 Views
0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All