Group and Sum Using a Filter

J

jgarza0422

New Member
Joined
Apr 8, 2019
Messages
10
Hello I am working an PQ view that will allow me to use the current column (Pipe Length) that I have added to sum but only if the column (PRGM_Code) is equal to"1". Ten I need to divide the (pipe Length) column by 12.

We I use the dialog box "Grouped By" there is not conditional formatting allowed which means I need to use the Advance Editor and I a not quite there yet. Can someone please give me she direction?

How do you upload a sample data file?

Thank n Advance,
Jgarza0422
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Just read no attachments allowed, so I have posted the query from the Advance Editor. The last bold line is where I believe I need the help.

let
Source = Excel.Workbook(File.Contents("x:\xxxxx.xls"), null, true),
PIPE1 = Source{[Name="PIPE"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(PIPE1, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID_COUNT", Int64.Type}, {"MODIFY", Int64.Type}, {"DWG_NAME", type text}, {"ALPHA_SIZE", type text}, {"MAIN_NOM", type number}, {"RED_NOM", type number}, {"MAIN_ACT", type number}, {"RED_ACT", type number}, {"SHORT_DESC", type text}, {"LONG_DESC", type text}, {"TAG", type text}, {"LINE_NUM", type text}, {"DB_CODE", type text}, {"DRAW_MODE", type text}, {"LINE_MODE", type text}, {"SYS_MODE", type text}, {"FIT_MODE", type text}, {"SPEC_FILE", type text}, {"LIB_FILE", type text}, {"DAT_FILE", type text}, {"PRGM_NAME", type text}, {"LENGTH", type number}, {"WEIGHT", type number}, {"THK_NOM", type number}, {"THK_RED", type number}, {"FLAG", Int64.Type}, {"PRGM_CODE", Int64.Type}, {"SORT_SEQ", Int64.Type}, {"PT0_X", type number}, {"PT0_Y", type number}, {"PT0_Z", type number}, {"PT1_X", type number}, {"PT1_Y", type number}, {"PT1_Z", type number}, {"PT2_X", type number}, {"PT2_Y", type number}, {"PT2_Z", type number}, {"PT3_X", type number}, {"PT3_Y", type number}, {"PT3_Z", type number}, {"PT4_X", type number}, {"PT4_Y", type number}, {"PT4_Z", type number}, {"PT5_X", type number}, {"PT5_Y", type number}, {"PT5_Z", type number}, {"RESERVE_01", type text}, {"RESERVE_02", type text}, {"RESERVE_03", type text}, {"RESERVE_04", type text}, {"RESERVE_05", type text}, {"RESERVE_06", type text}, {"RESERVE_07", type text}, {"RESERVE_08", type text}, {"RESERVE_09", type text}, {"RESERVE_10", type text}, {"PAR6", type text}, {"SOL_HANDLE", type text}, {"PRN_HANDLE", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ID_COUNT", "MODIFY"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"LINE_NUM", "DWG_NAME", "ALPHA_SIZE", "MAIN_NOM", "RED_NOM", "MAIN_ACT", "RED_ACT", "SHORT_DESC", "LONG_DESC", "TAG", "DB_CODE", "DRAW_MODE", "LINE_MODE", "SYS_MODE", "FIT_MODE", "SPEC_FILE", "LIB_FILE", "DAT_FILE", "PRGM_NAME", "LENGTH", "WEIGHT", "THK_NOM", "THK_RED", "FLAG", "PRGM_CODE", "SORT_SEQ", "PT0_X", "PT0_Y", "PT0_Z", "PT1_X", "PT1_Y", "PT1_Z", "PT2_X", "PT2_Y", "PT2_Z", "PT3_X", "PT3_Y", "PT3_Z", "PT4_X", "PT4_Y", "PT4_Z", "PT5_X", "PT5_Y", "PT5_Z", "RESERVE_01", "RESERVE_02", "RESERVE_03", "RESERVE_04", "RESERVE_05", "RESERVE_06", "RESERVE_07", "RESERVE_08", "RESERVE_09", "RESERVE_10", "PAR6", "SOL_HANDLE", "PRN_HANDLE"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "LINE_NUM", "LINE_NUM - Copy"),
#"Reordered Columns1" = Table.ReorderColumns(#"Duplicated Column",{"LINE_NUM", "LINE_NUM - Copy", "DWG_NAME", "ALPHA_SIZE", "MAIN_NOM", "RED_NOM", "MAIN_ACT", "RED_ACT", "SHORT_DESC", "LONG_DESC", "TAG", "DB_CODE", "DRAW_MODE", "LINE_MODE", "SYS_MODE", "FIT_MODE", "SPEC_FILE", "LIB_FILE", "DAT_FILE", "PRGM_NAME", "LENGTH", "WEIGHT", "THK_NOM", "THK_RED", "FLAG", "PRGM_CODE", "SORT_SEQ", "PT0_X", "PT0_Y", "PT0_Z", "PT1_X", "PT1_Y", "PT1_Z", "PT2_X", "PT2_Y", "PT2_Z", "PT3_X", "PT3_Y", "PT3_Z", "PT4_X", "PT4_Y", "PT4_Z", "PT5_X", "PT5_Y", "PT5_Z", "RESERVE_01", "RESERVE_02", "RESERVE_03", "RESERVE_04", "RESERVE_05", "RESERVE_06", "RESERVE_07", "RESERVE_08", "RESERVE_09", "RESERVE_10", "PAR6", "SOL_HANDLE", "PRN_HANDLE"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns1", "LINE_NUM - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"LINE_NUM - Copy.1", "LINE_NUM - Copy.2", "LINE_NUM - Copy.3", "LINE_NUM - Copy.4"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"LINE_NUM - Copy.1", "LINE_NUM_PROCESS"}, {"LINE_NUM - Copy.2", "LINE_NUM_ID"}, {"LINE_NUM - Copy.3", "LINE_NUM_WBS"}, {"LINE_NUM - Copy.4", "LINE_NUM_ISOSHT_NUM"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"LINE_NUM", "LINE_NUM_PROCESS", "LINE_NUM_ID", "LINE_NUM_WBS", "LINE_NUM_ISOSHT_NUM", "ALPHA_SIZE", "LONG_DESC", "DB_CODE", "PRGM_CODE"}, {{"Pipe Lenth", each List.Sum([LENGTH]), type number}, {"Qty", each Table.RowCount(_), type number}})
in
#"Grouped Rows"
 
Upvote 0
use google drive, one drive , drop box or any similar
share file with representative source data and expected result
post link to the shared file here
 
Upvote 0
as I said source data is required but I see:
DataSource.Error: Could not find a part of the path 'S:\_CADSupport\Piping\CWorx\Specs 19\NextWave\SMAT_ISO.xlsx'.

so the PIPE sheet is your source data?

btw. PowerQuery doesn't support Conditional Formatting
 
Upvote 0
Yes, the data should be using the PIPE worksheet. Its should all be with the same file I uploaded. I think I probably meant to use If then statement. what I wanted to do in the end was combine the last 2 columns into 1 if possible. I am just learning Power Query and trying to use the Access skills to have the same results when using an If statement. Thank for your patience.

Thank,
 
Upvote 0
you want merge two last columns: SQL & PRN into one? are you sure?

from your M-code I did:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]LINE_NUM[/td][td=bgcolor:#70AD47]ALPHA_SIZE[/td][td=bgcolor:#70AD47]Length[/td][td=bgcolor:#70AD47]Qty[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-12345-12000-01[/td][td=bgcolor:#E2EFDA]4"x3"[/td][td=bgcolor:#E2EFDA]
4.12​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-12345-12000-01[/td][td]3"[/td][td]
2​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-12345-12000-01[/td][td=bgcolor:#E2EFDA]4"[/td][td=bgcolor:#E2EFDA]
62.119972​
[/td][td=bgcolor:#E2EFDA]
9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-12345-12000-02[/td][td]4"x3"[/td][td]
4​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-12345-12000-02[/td][td=bgcolor:#E2EFDA]3"[/td][td=bgcolor:#E2EFDA]
49.33269​
[/td][td=bgcolor:#E2EFDA]
6​
[/td][/tr]
[/table]

Code: 
[SIZE=1]// Query1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"LINE_NUM", "ALPHA_SIZE"}, {{"Length", each List.Sum([LENGTH]), type number}, {"Qty", each Table.RowCount(_), type number}}),
    Sort = Table.Sort(Group,{{"LINE_NUM", Order.Ascending}})
in
    Sort[/SIZE]
and now you can split Line_Num as you wish

I don't understand why you duplicate Line_Num column then split and grouping the same columns, IMO it doesn't make sense
 
Last edited:
Upvote 0
Ops, sorry I lost three columns from grouping :)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]LINE_NUM[/td][td=bgcolor:#70AD47]ALPHA_SIZE[/td][td=bgcolor:#70AD47]LONG_DESC[/td][td=bgcolor:#70AD47]DB_CODE[/td][td=bgcolor:#70AD47]PRGM_CODE[/td][td=bgcolor:#70AD47]Length[/td][td=bgcolor:#70AD47]Qty[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-12345-12000-01[/td][td=bgcolor:#E2EFDA]4"[/td][td=bgcolor:#E2EFDA]Pipe ; ASME-B36.19M ; BE ; ASTM A312 Grade TP304/TP304L ; EFW (E = 0.80)[/td][td=bgcolor:#E2EFDA]PPPABRBEAWCAAQZ/PP0SS0000879[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
36​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-12345-12000-01[/td][td]4"[/td][td]90 Deg. Elbow 1D ; ASME B16.28 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td]BE9SABBBEAYXABFZ/E9SSS0000616[/td][td]
2​
[/td][td]
12​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-12345-12000-01[/td][td=bgcolor:#E2EFDA]4"x3"[/td][td=bgcolor:#E2EFDA]Red. Tee ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td=bgcolor:#E2EFDA]BTERABMBEAYXABFZ/TERSS0000190[/td][td=bgcolor:#E2EFDA]
9​
[/td][td=bgcolor:#E2EFDA]
4.12​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-12345-12000-01[/td][td]4"[/td][td]45 Deg. Elbow 1D ; Gen manu ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td]BE4SAP2BEAYXABFZ/E4SSS0000014[/td][td]
5​
[/td][td]
4.999972​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-12345-12000-01[/td][td=bgcolor:#E2EFDA]4"[/td][td=bgcolor:#E2EFDA]Equal Tee ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td=bgcolor:#E2EFDA]BTEEABMBEAYXABFZ/TEESS0000661[/td][td=bgcolor:#E2EFDA]
8​
[/td][td=bgcolor:#E2EFDA]
4.12​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-12345-12000-01[/td][td]4"[/td][td]Cap ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td]BCAPABMBEAYXABFZ/CAPSS0000072[/td][td]
12​
[/td][td]
5​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-12345-12000-01[/td][td=bgcolor:#E2EFDA]3"[/td][td=bgcolor:#E2EFDA]Cap ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td=bgcolor:#E2EFDA]BCAPABMBEAYXABFZ/CAPSS0000071[/td][td=bgcolor:#E2EFDA]
12​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-12345-12000-02[/td][td]4"x3"[/td][td]Con. Reducer ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td]BRECABMBEAYXABFZ/RECSS0002440[/td][td]
10​
[/td][td]
4​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-12345-12000-02[/td][td=bgcolor:#E2EFDA]3"[/td][td=bgcolor:#E2EFDA]Equal Tee ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td=bgcolor:#E2EFDA]BTEEABMBEAYXABFZ/TEESS0000665[/td][td=bgcolor:#E2EFDA]
8​
[/td][td=bgcolor:#E2EFDA]
3.38​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-12345-12000-02[/td][td]3"[/td][td]Pipe ; ASME-B36.19M ; BE ; ASTM A312 Grade TP304/TP304L ; EFW (E = 0.80)[/td][td]PPPABRBEAWCAAQZ/PP0SS0000878[/td][td]
1​
[/td][td]
37.45269​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P-12345-12000-02[/td][td=bgcolor:#E2EFDA]3"[/td][td=bgcolor:#E2EFDA]Cap ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td=bgcolor:#E2EFDA]BCAPABMBEAYXABFZ/CAPSS0000071[/td][td=bgcolor:#E2EFDA]
12​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]P-12345-12000-02[/td][td]3"[/td][td]90 Deg. Elbow 1D ; ASME B16.28 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W[/td][td]BE9SABBBEAYXABFZ/E9SSS0000615[/td][td]
2​
[/td][td]
4.5​
[/td][td]
1​
[/td][/tr]
[/table]
Code: 
[SIZE=1]// Query1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"LINE_NUM", "ALPHA_SIZE", "LONG_DESC", "DB_CODE", "PRGM_CODE"}, {{"Length", each List.Sum([LENGTH]), type number}, {"Qty", each Table.RowCount(_), type number}}),
    Sort = Table.Sort(Group,{{"LINE_NUM", Order.Ascending}})
in
    Sort[/SIZE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Query filtering using Today() as a date ?
Replies
8
Views
516
alansidman
A
T
  • Solved
Power Query: Group by - Output Multiple Sum Columns
Replies
1
Views
3K
takoyaki
T
K
  • Solved
Power Query - Function (Average Daily Balance) - Repeat for Each Dynamically Named Column and Dynamically Changing Number of Columns
Replies
7
Views
2K
KnightFhawker
K
O
  • Question Question
Power Query removes formulas from custom columns on refresh
Replies
0
Views
2K
One Handed
O
J
  • Solved
Optimising DAX Measures
Replies
2
Views
1K
JB2020
J

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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