none
Concatenate where a condition is met RRS feed

  • Question

  • Hi,

    In Power Query, is there a way to concatenate where a condition is met.  So let us say that we have a two column table with an Alphanumeric code in the first column and a text entry in another column.

    Code                Name
    A001                Henry
    A001                Sunny
    A001                David
    A002                Roger
    A002                Alfred

    I want to create another column which should show Henry,Sunny,David against codes A001 and Roger,Alfred against codes A002.  Please note that the solution should be dynamic for more rows that will be added.

    Code                Name                 Result
    A001                Henry                 Henry,Sunny,David
    A001                Sunny                Henry,Sunny,David
    A001                David                 Henry,Sunny,David
    A002                Roger                 Roger,Alfred
    A002                Alfred                 Roger,Alfred

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, September 30, 2017 6:33 AM

Answers

  • Ashish: in your query, the #"Added Column" step is wrong. Unlike the code from Imke, you have 2x Table.AddColumn

    = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count][[Hero]], ", ")))

    • Marked as answer by Ashish Mathur Sunday, October 1, 2017 1:52 AM
    Saturday, September 30, 2017 11:45 AM
  • Marcel is right here (as always :) )

    It looks as if you tried to create the solution by using the UI. In that case, you just have to take the code for the AddColumn-step, that comes after the "each". I find this easy to remember if I imagine that "each" means that the following commands are executed for each row of the table.

    So just enter Text.Combine([Count][Hero], ", ") into the dialogue of the pop-up-window, then the following code will be generated automatically for the whole step:

    Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count][Hero], ", ")))

    (!! no double-brackets around Hero !!)

    So you have to decide:

    1) Either take the full code and replace all items to match your table/data OR

    2) Try to re-create the steps using the UI, but then you have to select the code that is relevant.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Ashish Mathur Sunday, October 1, 2017 1:52 AM
    Saturday, September 30, 2017 3:41 PM
    Moderator

All replies

  • Yes, you group on code and select "All rows" in operations (just leave everything else as default). Then add a column that concatenates the names (Text.Combine([Count][Name], ", ")) and expand "name" from the grouped results.

    This should result in the following code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Name", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Count", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count][Name], ", ")),
        #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Name"}, {"Name"})
    in
        #"Expanded Count"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Proposed as answer by MarcelBeug Saturday, September 30, 2017 11:46 AM
    Saturday, September 30, 2017 7:18 AM
    Moderator
  • Hi,

    That code does not work. When I perform the last step of Expanding, I get a message saying that Cannot convert type table to type list.

    Anyways, my actual data looks like this

    Name ID Country Hero Project ID Amount
    1 Argentina Batman 1000 10
    1 Argentina Superman 1001 20
    2 USA Aquaman 1002 30
    2 USA Batman 1003 40
    3 Hungary Flash 1004 50
    3 Hungary Wonder Woman 1005 60
    4 Russia Batman 1006 70
    4 Russia Wonder Woman 1007 80
    5 Colombia Aquaman 1008 90
    5 Colombia Wonder Woman 1009 100

    In 1 5th column, I want to concatenate the superheroes for every Name ID.  So the result I am expecting is Batman,Superman for ID 1.  Then Aquaman,Batman for ID 2 and so on.

    Could you help please.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, September 30, 2017 8:49 AM
  • My code works. I've replaced the Source with some sample data, so you just have to paste the code into the advanced editor and will see the results like you've described above:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUfJIzSuqVIrVgQsEl+bloQi4JJZlpsAEjIACQfnpqUXIAo45aUWpQCWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Name = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Name", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Count", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count][Name], ", ")),
        #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Name"}, {"Name"})
    in
        #"Expanded Count"

    If you're having difficulties in applying it to your data, please paste your code here so that I can check it out.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, September 30, 2017 8:55 AM
    Moderator
  • Hi,

    Thank you.  Could you modify your code based on the sample that I have pasted above.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, September 30, 2017 9:05 AM
  • I made it a function, so you don't have to tweak the code:

    (Table as table, Group as text, Concatenate as text) =>
    
    let
        #"Grouped Rows" = Table.Group(Table, {Group}, {{"Expand", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine(Table.Column([Expand], Concatenate), ", ")),
        Result = Table.ExpandTableColumn(#"Added Custom", "Expand", List.Difference(Table.ColumnNames(#"Added Custom"[Expand]{0}), {Group}))
    
    in
    Result

    When calling this function, reference your source-table in the 1st parameter, and fill in the names of your columns in the others like this:

    FunctionName(YourTable, "Name ID", "Hero")


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, September 30, 2017 9:27 AM
    Moderator
  • Hi,

    Thank you for your help so far.  Could you share the non function solution please.  I do not want the function - just the code with respect to the data that I shared above.

    Thank you for your help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, September 30, 2017 9:44 AM
  • I gave the code in non-function-form already. All you have to do is to replace the reference to your source data and the column names to your new ones.

    My offer to check your code for errors is still valid. This would be easiest if you share your workbook.

    That's all I can offer.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, September 30, 2017 10:22 AM
    Moderator
  • Hi,

    Thank you for your help so far.  Please download my workbook from here.  In the Power Query window, when I click on the opposite arrows of the Custom column, I get an error.

    Thank you once again.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, September 30, 2017 11:18 AM
  • Ashish: in your query, the #"Added Column" step is wrong. Unlike the code from Imke, you have 2x Table.AddColumn

    = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count][[Hero]], ", ")))

    • Marked as answer by Ashish Mathur Sunday, October 1, 2017 1:52 AM
    Saturday, September 30, 2017 11:45 AM
  • Marcel is right here (as always :) )

    It looks as if you tried to create the solution by using the UI. In that case, you just have to take the code for the AddColumn-step, that comes after the "each". I find this easy to remember if I imagine that "each" means that the following commands are executed for each row of the table.

    So just enter Text.Combine([Count][Hero], ", ") into the dialogue of the pop-up-window, then the following code will be generated automatically for the whole step:

    Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count][Hero], ", ")))

    (!! no double-brackets around Hero !!)

    So you have to decide:

    1) Either take the full code and replace all items to match your table/data OR

    2) Try to re-create the steps using the UI, but then you have to select the code that is relevant.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Ashish Mathur Sunday, October 1, 2017 1:52 AM
    Saturday, September 30, 2017 3:41 PM
    Moderator
  • Hey @Ashish,

    Here's the workbook with the solution that, I believe, you're looking.

    Best!

    Saturday, September 30, 2017 5:04 PM
  • Thank you very much for your help.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Sunday, October 1, 2017 1:52 AM
  • Thank you very much for your help.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Sunday, October 1, 2017 1:53 AM
  • Hi,

    Thank you very much for responding.  Imke's solution worked quite well.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Sunday, October 1, 2017 1:54 AM