Power Query average

[Tigerexcel]

Input on left, output to the right (showing what the first record would look like) which has an average column as well as subjects separated into columns.

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Results		Name	Average	Unit 1	Unit 2	Unit 3	Unit 4
2	Tom	ABC6010:C:63, ABC6020:C:66, ABC6080:C:62, ABC6090:D:75,		Tom	66.5	ABC6010:63:C	ABC6020:66:C	ABC6080:62:C	ABC6090:75:D
3	Fred	ABC2020:D:75, ABC2040:P:57, ABC2050:C:63, ABC2080:N:48,
4	Harry	ABC6010:C:63, ABC6020:C:62, ABC6080:D:72, ABC6090:P:56,
5	Sue	ABC2020:C:62, ABC2040:C:64, ABC2050:C:63, ABC2080:C:68,
Sheet1

 

[Tigerexcel]

Love the economy of your code there Sandy, can you provide a little more detail regarding the third instruction where you manage to Split the data in one move?

 

[sandy666]

You mean this line?

   Split = Table.SplitColumn(Extract, "Results", Splitter.SplitTextByAnyDelimiter({":",","}, QuoteStyle.Csv))

syntax:

Splitter.SplitTextByAnyDelimiter(delimiters as list, optional quoteStyle as nullable number, optional startAtEnd as nullable logical) as function

Returns a function that splits text into a list of text at any of the specified delimiters.

 

[Tigerexcel]

Yes, that's the one. That is not a standard function and involves some code manipulation, does it not?

 

[sandy666]

What does that mean: standard ?
I would say "not accessible from the ribbon" rather than "not standard"
see "standard" functions: Power Query M function reference - PowerQuery M

 

[sandy666]

see main differences

not from the ribbon

Splitter.SplitTextByAnyDelimiter(delimiters as list, optional quoteStyle as nullable number,optional startAtEnd as nullable logical) as function

from the ribbon

Splitter.SplitTextByDelimiter(delimiter as text, optional quoteStyle as nullable number) as function

 

[Tigerexcel]

see main differences

not from the ribbon

Splitter.SplitTextByAnyDelimiter(delimiters as list, optional quoteStyle as nullable number,optional startAtEnd as nullable logical) as function

from the ribbon

Splitter.SplitTextByDelimiter(delimiter as text, optional quoteStyle as nullable number) as function

Thanks for the clarification Sandy.

 

[sandy666]

You are welcome
Thanks for the feedback & have a nice day