SQL in Microsoft Query to join 4 selects and sum results in one column

Question

I have an Excel table with this data:

Micro |Group | Series|Group1 |Series1|Group2|Series2|Group3|Series3|
  1   |Back  |   3   |Biceps |   1   |Delts |   1   |Traps |   1   |
  1   |Chest |   4   |Triceps|   2   |Delts |   2   |      |       |
  1   |Biceps|   2   |       |       |      |       |      |       |
  1   |Cuads |   4   |Glut   |   2   |      |       |      |       |
  2   |Back  |   4   |Biceps |   2   |Delts |   2   |Traps |   2   |
  2   |Chest |   5   |Triceps|   3   |Delts |   3   |      |       |
  2   |Biceps|   3   |       |       |      |       |      |       |
  2   |Cuads |   5   |Glut   |   3   |      |       |      |       |
....

I want to generate a table witch shows amount of series of each micro by group adding Series+Series1+Series2+Series3:

Micro | Group  | Series|
  1   | Back   |   3   |
  1   | Chest  |   4   |
  1   | Biceps |   3   |
  1   | Cuads  |   4   |
  1   | Triceps|   2   |
  1   | Delts  |   3   |
  1   | Traps  |   1   |
  2   | Back   |   4   |
  2   | Chest  |   5   |
  2   | Biceps |   5   |
  2   | Cuads  |   5   |
  2   | Triceps|   3   |
  2   | Delts  |   5   |
  2   | Traps  |   2   |

I have very basic SQL skills and do not know how to perform a query in Microsoft Query to get this table.

Thanks!

Is using `MS Query` a must? This task can be done in Power Query, available in Excel 2010+ — Ron Rosenfeld, Sep 26 '20 at 00:12
Hi! @RonRosenfeld! I did not know it could be done in Power Query. Please, how could I do it? Thanks! — Jonha, Sep 26 '20 at 08:24

score 1 · Answer 1 · answered Jan 13 '21 at 08:00

Start from the second column, and every two columns make a pair, there are a total of 4 pairs of them. The key point is to convert the N-column table into a table consisting of 3 columns. Using esProc is much easy.

    A
1   =file("data.xlsx").xlsimport@w().to(2,)
2   =A1.news(~.len()\2;A1.~(1):Micro,A1.~(#*2):Group,A1.~(#*2+1):Series)
3   =A2.groups(Micro,Group;sum(Series):Series)
4   =file("result.xlsx").xlsexport@t(A3)

For more reference, see http://c.raqsoft.com/article/1609815706959

DISCLAIMER: This is about our tool esProc. It’s freemium.

score 0 · Answer 2 · answered Sep 26 '20 at 11:18

To do this with Power Query, the basic steps are

Unpivot
Filter out the blanks
Group in pairs (Group/Series)
Extract the data
split into new columns

Below is the M-Code. You can explore this by examining the "Applied Steps" where you can see the comments I made; and also, by clicking or double-clicking on each step, you can see what is being done.

There may be more efficient methods of doing this, as I am a relative beginner with PQ, but this should get you started.

BTW, the result will not be in the same order as you show, as PQ pulls things together row-wise rather than column-wise. But you can insert a sorting option if you need the results in a particular order.

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Micro", Int64.Type}, {"Group", type text}, {"Series", Int64.Type}, {"Group1", type text}, {"Series1", Int64.Type}, {"Group2", type text}, {"Series2", Int64.Type}, {"Group3", type text}, {"Series3", Int64.Type}}),
    
    //select Micro column and unpivot
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Micro"}, "Attribute", "Value"),

    //filter out the blanks
    #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),

    //Split off the terminal number, so as to leave identical "former"column headers
    #"Split Column by Character Transition" = Table.SplitColumn(#"Filtered Rows1", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),

    //add index and create a series for grouping in pairs
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),

    //Group by the pairs
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Integer-Division"}, {{"Grouped", each _, type table [#"Micro "=nullable number, Attribute.1=nullable text, Value=any, #"Integer-Division"=number]}}),

    //extract the Micro and Value columns from each grouped table
    //then split into new columns
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Group", each List.Combine({{List.First(Table.Column([Grouped],"Micro"))},
Table.Column([Grouped],"Value")})),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Group", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Group", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Group.1", "Group.2", "Group.3"}),

    //cleanup - set data types, remove unneeded columns
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Group.1", Int64.Type}, {"Group.2", type text}, {"Group.3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Group.1", "Micro"}, {"Group.2", "Group"}, {"Group.3", "Series"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Integer-Division", "Grouped"}),

    //Group by "Group" and SUM
    #"Grouped Rows1" = Table.Group(#"Removed Columns2", {"Micro", "Group"}, {{"Series", each List.Sum([Series]), type nullable number}})
in
    #"Grouped Rows1"

SQL in Microsoft Query to join 4 selects and sum results in one column

2 Answers2