Power Query average

T

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
ABCDEFGHI
1NameResultsNameAverageUnit 1Unit 2Unit 3Unit 4
2TomABC6010:C:63, ABC6020:C:66, ABC6080:C:62, ABC6090:D:75, Tom66.5ABC6010:63:CABC6020:66:CABC6080:62:CABC6090:75:D
3FredABC2020:D:75, ABC2040:P:57, ABC2050:C:63, ABC2080:N:48,
4HarryABC6010:C:63, ABC6020:C:62, ABC6080:D:72, ABC6090:P:56,
5SueABC2020:C:62, ABC2040:C:64, ABC2050:C:63, ABC2080:C:68,
Sheet1
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Book8
ABCDEF
1NameUnit 1Unit 2Unit 3Unit4Average
2TomABC6010:C:63 ABC6020:C:66 ABC6080:C:62 ABC6090:D:7566.5
3FredABC2020:D:75 ABC2040:P:57 ABC2050:C:63 ABC2080:N:4860.75
4HarryABC6010:C:63 ABC6020:C:62 ABC6080:D:72 ABC6090:P:5663.25
Sheet2


Rich (BB code): 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Results", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Results.1", "Results.2", "Results.3", "Results.4", "Results.5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Results.5"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Results.1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Results.1.1", "Results.1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Results.1.1", type text}, {"Results.1.2", Int64.Type}, {"Results.2", type text}, {"Results.3", type text}, {"Results.4", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type", "Results.2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Results.2.1", "Results.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Results.2.1", type text}, {"Results.2.2", Int64.Type}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type1", "Results.3", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Results.3.1", "Results.3.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Results.3.1", type text}, {"Results.3.2", Int64.Type}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type2", "Results.4", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Results.4.1", "Results.4.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Results.4.1", type text}, {"Results.4.2", Int64.Type}}),
    #"Inserted Average" = Table.AddColumn(#"Changed Type3", "Average", each List.Average({[Results.1.2], [Results.2.2], [Results.3.2], [Results.4.2]}), type number),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Average", {{"Results.1.2", type text}}, "en-US"),{"Results.1.1", "Results.1.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 1"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Results.2.2", type text}}, "en-US"),{"Results.2.1", "Results.2.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 2"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Results.3.2", type text}}, "en-US"),{"Results.3.1", "Results.3.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 3"),
    #"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns2", {{"Results.4.2", type text}}, "en-US"),{"Results.4.1", "Results.4.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit4")
in
    #"Merged Columns3"
 
Upvote 0
a bit shorter
Rich (BB code): 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Ren = Table.RenameColumns(Source,{{"Results", "Unit"}}),
    Extract = Table.TransformColumns(Ren, {{"Unit", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}}),
    Split1 = Table.SplitColumn(Extract, "Unit", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Unit.1", "Unit.2", "Unit.3", "Unit.4"}),
    Split2 = Table.SplitColumn(Split1, "Unit.1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Unit.1", "Unit.1.2"}),
    Split3 = Table.SplitColumn(Split2, "Unit.2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Unit.2", "Unit.2.2"}),
    Split4 = Table.SplitColumn(Split3, "Unit.3", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Unit.3", "Unit.3.2"}),
    Split5 = Table.SplitColumn(Split4, "Unit.4", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Unit.4", "Unit.4.2"}),
    Type = Table.TransformColumnTypes(Split5,{{"Unit.1.2", type number}, {"Unit.2.2", type number}, {"Unit.3.2", type number}, {"Unit.4.2", type number}}),
    Avg = Table.AddColumn(Type, "Average", each List.Average({[Unit.1.2], [Unit.2.2], [Unit.3.2], [Unit.4.2]}), type number),
    TSC = Table.SelectColumns(Avg,{"Name", "Average", "Unit.1", "Unit.2", "Unit.3", "Unit.4"})
in
    TSC
 
Upvote 0
Thanks Alan for the quick response but I need the letter grade to proceed the numerical grade ie ABC2010:63:C
 
Upvote 0
NameAverageUnit.1Unit.2Unit.3Unit.4
Tom66.5ABC6010:C ABC6020:C ABC6080:C ABC6090:D
Fred60.75ABC2020:D ABC2040:P ABC2050:C ABC2080:N
Harry63.25ABC6010:C ABC6020:C ABC6080:D ABC6090:P
Sue64.25ABC2020:C ABC2040:C ABC2050:C ABC2080:C
 
Upvote 0
Thank you Sandy, perfect. I like the brevity of steps, My approach had 20 steps and still gave me the wrong answer.
 
Upvote 0
sandy666 said:
You are welcome
Thanks for the feedback :biggrin: (y)
Click to expand...
c
Sandy, I just realised, is it possible to include the numerical component ie ABC6010:63:C rather than ABC6010:C? The critical thing is that the letter comes last but having the number there answers other potential questions.
 
Upvote 0
Rich (BB code): 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Ren = Table.RenameColumns(Source,{{"Results", "Unit"}}),
    Extract = Table.TransformColumns(Ren, {{"Unit", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}}),
    Split1 = Table.SplitColumn(Extract, "Unit", Splitter.SplitTextByAnyDelimiter({":",","}, QuoteStyle.Csv)),
    Type = Table.TransformColumnTypes(Split1,{{"Unit.3", type number}, {"Unit.6", type number}, {"Unit.9", type number}, {"Unit.12", type number}}),
    Avg = Table.AddColumn(Type, "Average", each List.Average({[Unit.3], [Unit.6], [Unit.9], [Unit.12]}), type number),
    Unit1 = Table.CombineColumns(Table.TransformColumnTypes(Avg, {{"Unit.3", type text}}, "en-GB"),{"Unit.1", "Unit.3", "Unit.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 1"),
    Unit2 = Table.CombineColumns(Table.TransformColumnTypes(Unit1, {{"Unit.6", type text}}, "en-GB"),{"Unit.4", "Unit.6", "Unit.5"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 2"),
    Unit3 = Table.CombineColumns(Table.TransformColumnTypes(Unit2, {{"Unit.9", type text}}, "en-GB"),{"Unit.7", "Unit.9", "Unit.8"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 3"),
    Unit4 = Table.CombineColumns(Table.TransformColumnTypes(Unit3, {{"Unit.12", type text}}, "en-GB"),{"Unit.10", "Unit.12", "Unit.11"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 4"),
    TSC = Table.SelectColumns(Unit4,{"Name", "Average", "Unit 1", "Unit 2", "Unit 3", "Unit 4"})
in
    TSC
NameAverageUnit 1Unit 2Unit 3Unit 4
Tom66.5ABC6010:63:C ABC6020:66:C ABC6080:62:C ABC6090:75:D
Fred60.75ABC2020:75:D ABC2040:57:P ABC2050:63:C ABC2080:48:N
Harry63.25ABC6010:63:C ABC6020:62:C ABC6080:72:D ABC6090:56:P
Sue64.25ABC2020:62:C ABC2040:64:C ABC2050:63:C ABC2080:68:C
 
Upvote 0
a bit bit shorter :biggrin: :biggrin: :biggrin:

Rich (BB code): 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.TransformColumns(Source, {{"Results", each Text.BeforeDelimiter(_, ",", {0, RelativePosition.FromEnd}), type text}}),
    Split = Table.SplitColumn(Extract, "Results", Splitter.SplitTextByAnyDelimiter({":",","}, QuoteStyle.Csv)),
    Type = Table.TransformColumnTypes(Split,{{"Results.3", type number}, {"Results.6", type number}, {"Results.9", type number}, {"Results.12", type number}}),
    Avg = Table.AddColumn(Type, "Average", each List.Average({[Results.3], [Results.6], [Results.9], [Results.12]}), type number),
    Unit1 = Table.CombineColumns(Table.TransformColumnTypes(Avg, {{"Results.3", type text}}, "en-GB"),{"Results.1", "Results.3", "Results.2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 1"),
    Unit2 = Table.CombineColumns(Table.TransformColumnTypes(Unit1, {{"Results.6", type text}}, "en-GB"),{"Results.4", "Results.6", "Results.5"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 2"),
    Unit3 = Table.CombineColumns(Table.TransformColumnTypes(Unit2, {{"Results.9", type text}}, "en-GB"),{"Results.7", "Results.9", "Results.8"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 3"),
    Unit4 = Table.CombineColumns(Table.TransformColumnTypes(Unit3, {{"Results.12", type text}}, "en-GB"),{"Results.10", "Results.12", "Results.11"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Unit 4"),
    TSC = Table.SelectColumns(Unit4,{"Name", "Average", "Unit 1", "Unit 2", "Unit 3", "Unit 4"})
in
    TSC
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Updating data with Power Query when the updated data has an extra column and lookups are needed
2
Replies
12
Views
221
tigerzen
T
K
  • Question Question
Help with looping through an entire lists in Power Query
Replies
2
Views
517
JGordon11
JGordon11
S
  • Solved
Power BI Line chart visual
Replies
3
Views
371
shansakhi
S
J
  • Solved
Missy Record from Power Query
Replies
4
Views
473
jarett
J
A
  • Solved
Power Query to arrange 8 Different Headers as two different columns
Replies
5
Views
301
ashish128
A

Forum statistics

Threads
1,223,785
Messages
6,174,540
Members
452,571
Latest member
MarExcelTips

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top