Forum Discussion

Alex_Serebr's avatar
Alex_Serebr
Frequent Visitor
3 years ago
Solved

Combining filtered column values in Power Query

I am not so familiar with M lang, and am straggling to solve the following problem in Power Query:

 

I have the table 

TABLE1

DateAttributeValue
2023-02-01AAA1
2023-02-01AAA2
2023-02-01BBB1
2023-02-02AAA3
2023-02-03BBB2
2023-02-03BBB3
2023-02-04CCC1
2023-02-06AAA4

 

I need to combine (with ";" as separator) values in cells indicated above in italic so the only one row exist for every Date/Attribute combination. 

In other words, the desired table should looks like this:

 

DateAttributeValue
2023-02-01AAA1; A2
2023-02-01BBB1
2023-02-02AAA3
2023-02-03BBB2; B3
2023-02-04CCC1
2023-02-06AAA4

 

Please help!

 

 

Need Help
  • serpiva64's avatar
    serpiva64
    3 years ago

    Hi,

    You have to group by

    then add a custom column

    Table.ToList( Table.RemoveColumns([Count],{"Date", "Attribute"}))

    then extract values

    select your delimiter

    and remove column count

    If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

     

     

     

     

     

2 Replies

  • serpiva64's avatar
    serpiva64
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi,

    You have to group by

    then add a custom column

    Table.ToList( Table.RemoveColumns([Count],{"Date", "Attribute"}))

    then extract values

    select your delimiter

    and remove column count

    If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

     

     

     

     

     

  • Alex_Serebr's avatar
    Alex_Serebr
    Frequent Visitor
    3 years ago

    Thank you very much, serpiva64 !!!

    It turns much easier then I've tried to code with nested M functions.

    Thanks once again for very helpful reply.