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?

 

[Tigerexcel]

Hi again Sandy,

Just trying to do a simple group by, where the Rep is grouped so that we get Jack HR, Finance etc. then Fred Sales, Training etc.
I have to Convert from Table to List which normally works fine but the Nullable text entries seem to give me an error unless I remove them.

 

[sandy666]

Could you post expected result from your example (post#1) ?
I still don't understand your change in the grouping line,
you can simply add column with List.Distinct([Count][Rep])

 

[sandy666]

oops, should be: List.Distinct([Count][Position])

 

[Tigerexcel]

Could you post expected result from your example (post#1) ?
I still don't understand your change in the grouping line,
you can simply add column with List.Distinct([Count][Rep])

Desired result

Book3
	A	B
1	Rep	Count
2	Jack	HR,Finance,Mgt,IT
3	Fred	Sales,Training,Security
4	Pete	Admin
Sheet4

 

[sandy666]

Rep	List
Jack	HR,Finance,Mgt,IT
Fred	Sales,Training,Security
Pete	Admin

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

 

[Tigerexcel]

Thanks Sandy, my question is not so much how to solve this but:
1) where do the entries [Position=nullable text, Rep=nullable text] come from? I haven't seen them before
2) The code below indicates Token Comma error, can it be fixed by inserting a comma somewhere?

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

 

[sandy666]

[Position=nullable text, Rep=nullable text]

1. how you did it?
2. as you can see whole line is an error #!
3. check data types
4. you are trying to use "list" inside table type group

for more info how Power Query works

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

 

[sandy666]

maybe try
[Position = type nullable text, Rep = type nullable text]

 

[Tigerexcel]

1. how you did it?
2. as you can see whole line is an error #!
3. check data types
4. you are trying to use "list" inside table type group

for more info how Power Query works

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

When I remove the [Position=nullable text, Rep=nullable text] it works perfectly. I wonder if I have accidentally changed a setting which brings this statement up. I can't find much on this on the Internet.

 

[sandy666]

I wonder if I have accidentally changed a setting which brings this statement up

I don't know what you did and how you did it

The standard library does include a collection of functions to extract the defining characteristics from a custom
type, so specific compatibility tests can be implemented as M expressions. Below are some examples; consult the
M library specification for full details

Type.ListItem( type {number} )
// type number
Type.NonNullable( type nullable text )
// type text
Type.RecordFields( type [A=text, B=time] )
// [ A = [Type = type text, Optional = false],
// B = [Type = type time, Optional = false] ]
Type.TableRow( type table [X=number, Y=date] )
// type [X = number, Y = date]
Type.FunctionParameters(
type function (x as number, optional y as text) as number)
// [ x = type number, y = type nullable text ]
Type.FunctionRequiredParameters(
type function (x as number, optional y as text) as number)
// 1
Type.FunctionReturn(
type function (x as number, optional y as text) as number)
// type number