Nullable text?

[Tigerexcel]

Book3
	A	B
1	Position	Rep
2	HR	Jack
3	Sales	Fred
4	Finance	Jack
5	Admin	Pete
6	Mgt	Jack
7	Training	Fred
8	Security	Fred
9	IT	Jack
Sheet1

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Position", type text}, {"Rep", type text}}),
#"Grouped Rows" = #!"Table.Group(#""Changed Type"", {""Rep""}, {{""Count"", each [Rep], type list [Position=nullable text, Rep=nullable text]}})"
in
#"Grouped Rows"

Using PQ to return grouped by records, as in Jack HR, Finance, Mgt, IT
I get a Token comma expected message, I've tried inserting commas in various places but to no avail. When I remove the "[Position=nullable text, Rep=nullable text]" the command works and returns the grouped records.
What does the nullable text information come up as I haven't noticed it before? Where is the token comma supposed to go to make this work if I leave the nullable text info in?

 

[sandy666]

Is that what you want?

Table.Group(Source, {"Rep"}, {{"Count", each List.Distinct([Position]), type list, [Position = type nullable text, Rep = type nullable text]}})

 

[sandy666]

working M:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Rep"}, {{"Count", each List.Distinct([Position]), type list, [Position = type nullable text, Rep = type nullable text]}}),
    Extract = Table.TransformColumns(Group, {"Count",each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extract

 

[Tigerexcel]

OK, I think we're one step closer to solving this, thanks Sandy for your work so far.
The method I use to group rows mainly because it cuts out a step is as per

You will see at approx 2:30 into the video that a change is required to move from Table to List. In the Youtube video there is no declaration of type (Text) following type Table.
For some reason, and I think only in the last 2 weeks PQ now declares the types and adds [Field = type text] or [Field = type nullable text] after type Table.
I've now tried to group rows on another computer and the declaration occurs there as well so something has changed on PQ, I thought originally I may have altered one of my settings but evidently this is not the case. Why do these declarations come up and can they be removed?

 

[sandy666]

you got solution for post#1 in post#21

as I said before for more info read

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

Have a nice day

 

[Tigerexcel]

Thank you Sandy, I have read the documentation, I'm still not sure why the method I've been using for a few months no longer works. If it's because of the Automatically detect data type I can't change this setting.

 

[sandy666]

in your example data type line (#"Changed Type") is "nullable" line so you can remove it at the beginning. In your case all data is a text so can be type text or type any and has no effect on the rest of the code except for increasing the number of lines.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Position", type text}, {"Rep", type text}}),
    #"Grouped Rows" = #!"Table.Group(#""Changed Type"", {""Rep""}, {{""Count"", each [Rep], type list [Position=nullable text, Rep=nullable text]}})"
in
    #"Grouped Rows"

You can turn it off here

unfortunately it works for current workbook but not for all workbooks in the future
or
remove this line each time you create new query if you are sure it is not necessary

 

[sandy666]

Power Query is the art of predicting what the result will be if you use a function and how it will affect the final result, not just typing a function like "maybe it will work"
practice, practice and practice again this is not Click&Go only

 

[Tigerexcel]

T

Power Query is the art of predicting what the result will be if you use a function and how it will affect the final result, not just typing a function like "maybe it will work"
practice, practice and practice again this is not Click&Go only

Thanks Sandy for your help here. I agree with the practice but up until a few weeks ago my method for Grouping information worked fine and now without any change in my settings, it requires extra steps and an extra layer of understanding.

 

[sandy666]

You are welcome