none
New column with grouped concatenated values RRS feed

  • Question

  • Hi,

    I an trying to get Column4 auto calculated added based the values in column1/2 and 3. Value in column4 is the concatenated values of column1/2/3 for all values of column1. 

    I am quite new to this hence appreciate a little explanation on steps involved to get this done would help. 

    Column1 Column2 Column3 Column4
    AAA TEST1 10 AAA TEST1 10 TEST 2 15 TEST3 20
    AAA TEST2 15 AAA TEST1 10 TEST 2 15 TEST3 20
    AAA TEST3 20 AAA TEST1 10 TEST 2 15 TEST3 20
    BBB TEST1 11 BBB TEST1 11 TEST2 12 TEST6 10
    BBB TEST2 12 BBB TEST1 11 TEST2 12 TEST6 10
    BBB TEST6 10 BBB TEST1 11 TEST2 12 TEST6 10
    CCC TEST1 6 CCC TEST1 6 TEST3 4 TEST4 4
    DDD TEST3 4 DDD TEST1 6 TEST3 4 TEST4 4
    DDD TEST4 4 DDD TEST1 6 TEST3 4 TEST4 4

    Thanks
    Suji


    • Edited by _Suji Friday, September 20, 2019 4:10 AM
    Friday, September 20, 2019 4:07 AM

Answers

  • my mistake, try this:

    1. make a column col4 that concatenates your Column2 and Column3
    2. Group by Column1, and generate a concatenation of the list of the texts in col4 created in step #1
    3. expand
    4. concatenate your Column1 with the Column4 using Table.ReplaceValue


    let
    	Source = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],
    	AddCol = Table.AddColumn(#"Source", "col4", each _[Column2] & " " & Text.From(_[Column3])),
    	Group = Table.Group(
    		#"AddCol",
    		{"Column1"},
    		{
    			{"content", each _},
    			{"Column4", each Text.Combine(_[col4], " ")}
    
    		}
    	),
    	Expand = Table.ExpandTableColumn(#"Group", "content", {"Column2", "Column3"}, {"Column2", "Column3"}),
    	ReplaceValue = Table.ReplaceValue(#"Expand", each [Column1], null, (a,b,c)=> b & " " & a,  {"Column4"})
    in ReplaceValue




    • Edited by anthony34 Friday, September 20, 2019 7:40 AM
    • Marked as answer by _Suji Friday, September 20, 2019 8:10 AM
    Friday, September 20, 2019 7:36 AM

All replies

  • one approach:

    1. make a column col4 that concatenates your Column1, Column2 and Column3
    2. Group by Column1, and generate a concatenation of the list of the texts in col4 created in step #1
    3. expand
    let
    	Source = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],
    	AddCol = Table.AddColumn(#"Source", "col4", each [Column1] & " " & _[Column2] & " " & Text.From(_[Column3])),
    	Group = Table.Group(
    		#"AddCol",
    		{"Column1"},
    		{
    			{"content", each _, type table [Column1=text, Column2=text, Column3=number, col4=text]},
    			{"Column4", each Text.Combine(_[col4], " ")}
    
    		}
    	),
        Expand = Table.ExpandTableColumn(#"Group", "content", {"Column2", "Column3"}, {"Column2", "Column3"})
    in Expand

    • Edited by anthony34 Friday, September 20, 2019 7:05 AM
    Friday, September 20, 2019 7:05 AM
  • Dear Anthony,

    Column1 is getting added multiple times.. is there any way make that avoid that and add column1 only one.

    Thanks

    Suji


    Friday, September 20, 2019 7:21 AM
  • my mistake, try this:

    1. make a column col4 that concatenates your Column2 and Column3
    2. Group by Column1, and generate a concatenation of the list of the texts in col4 created in step #1
    3. expand
    4. concatenate your Column1 with the Column4 using Table.ReplaceValue


    let
    	Source = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],
    	AddCol = Table.AddColumn(#"Source", "col4", each _[Column2] & " " & Text.From(_[Column3])),
    	Group = Table.Group(
    		#"AddCol",
    		{"Column1"},
    		{
    			{"content", each _},
    			{"Column4", each Text.Combine(_[col4], " ")}
    
    		}
    	),
    	Expand = Table.ExpandTableColumn(#"Group", "content", {"Column2", "Column3"}, {"Column2", "Column3"}),
    	ReplaceValue = Table.ReplaceValue(#"Expand", each [Column1], null, (a,b,c)=> b & " " & a,  {"Column4"})
    in ReplaceValue




    • Edited by anthony34 Friday, September 20, 2019 7:40 AM
    • Marked as answer by _Suji Friday, September 20, 2019 8:10 AM
    Friday, September 20, 2019 7:36 AM
  • Perfect! Thanks for the help.
    Friday, September 20, 2019 8:11 AM