I know there's a better way

[Tigerexcel]

I'm trying to educate myself with PQ and would like to know how to do this more efficiently. I've got this from a youtube video

and one of the comments below is that the Convert to List from Table mode can be avoided through the use of Text.Combine but I can't get it to work and keep getting an error. The author of the video uses a Custom Column and I'd like to avoid it if it's not absolutely necessary. My desired output is the same as what the author ultimately gets.

My coding is ok up to this point, I just need to know how to do the Text.Combine part.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Name", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Count", each _, type table}})
in
#"Grouped Rows"

The data is:

Category	Name
Orange	Alex
Blue	Jerry
Gold	Frank
Black	Kas
Orange	Tim
Orange	Ted
Gold	Jake
Black	Fred
Orange	Harry
Blue	Noel
Gold	Jude
Black	Fingle
Orange	Eva
Blue	Jake
Gold	Ned

 

[GraH]

As Text.Combine works on a list, you first need to extract the column from the table as list.
Text.Combine(Table.Column([Count],"Name"),";")

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

 

[Tigerexcel]

Thanks G. I really appreciate the explanation. Really starting to see the range of what PQ can do but some of the syntax is confusing. Would love to know why you use ( ) as compared to { } as compared to [ ] as compared to " " etc.

 

[alansidman]

A really good primer for learning more about PQ is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Available on Amazon. They do step by step of the basics to help you understand the power of PQ.

 

[GraH]

A really good primer for learning more about PQ is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Available on Amazon. They do step by step of the basics to help you understand the power of PQ.

I second that it is indeed how I started.

 

[GraH]

Thanks G. I really appreciate the explanation. Really starting to see the range of what PQ can do but some of the syntax is confusing. Would love to know why you use ( ) as compared to { } as compared to [ ] as compared to " " etc.

As @alansidman suggests, understanding the basics for M-language is key to start mastering it. PQ on Microsoft Doc.

[ ] is a reference to a column when it appears like [ColumnName]
[ ] can be a record when it appears like [Category = "Blue", Name = "Alex"]
{ } is a list object
( ) like in excel, function arguments are wrapped between these + like in excel, you use them to define order of precedence.
" " are literals (text)

 

[Tigerexcel]

Thanks G and Alan, I have ordered the recommended book.
I've noticed that there's actually not a lot of books on M and that does make it a little more challenging. Still, it's a worthwhile pursuit and I love how on the Youtube videos the authors can change a word here and there in the code and get the outcome they want especially with combining text and merging records. I don't know why there wouldn't be a menu driven way of doing that but nevertheless I would say that is a time effective way of doing things that in the past I would have had to use macros/VBA for.
G., thank you for that reference to the brackets etc, you have no idea how useful that is to me and hopefully it will guide others as well. The linked MS document also is good.

 

[GraH]

Enjoy the read and study well, Tigerexcel. And thanks for the kind feedback.
First you think: "Wow, all this is possible by simply using the mouse?". Then you'll be tweaking the code here and there like you have seen in the videos. And before you know it you'll be writing custom M-functions.