Group and Sum Using a Filter

[jgarza0422]

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

 

[jgarza0422]

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"

 

[sandy666]

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

 

[jgarza0422]

Hope this works and helps. Thanks for the direction.

https://1drv.ms/f/s!Akhbdc_9A09Au1ceeZToFYDB-QPt

Thank,

 

[sandy666]

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

 

[jgarza0422]

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,

 

[sandy666]

you want merge two last columns: SQL & PRN into one? are you sure?

from your M-code I did:

LINE_NUM	ALPHA_SIZE	Length	Qty
P-12345-12000-01	4"x3"	4.12​	1​
P-12345-12000-01	3"	2​	1​
P-12345-12000-01	4"	62.119972​	9​
P-12345-12000-02	4"x3"	4​	1​
P-12345-12000-02	3"	49.33269​	6​

[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

 

[sandy666]

Ops, sorry I lost three columns from grouping

LINE_NUM	ALPHA_SIZE	LONG_DESC	DB_CODE	PRGM_CODE	Length	Qty
P-12345-12000-01	4"	Pipe ; ASME-B36.19M ; BE ; ASTM A312 Grade TP304/TP304L ; EFW (E = 0.80)	PPPABRBEAWCAAQZ/PP0SS0000879	1​	36​	2​
P-12345-12000-01	4"	90 Deg. Elbow 1D ; ASME B16.28 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BE9SABBBEAYXABFZ/E9SSS0000616	2​	12​	2​
P-12345-12000-01	4"x3"	Red. Tee ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BTERABMBEAYXABFZ/TERSS0000190	9​	4.12​	1​
P-12345-12000-01	4"	45 Deg. Elbow 1D ; Gen manu ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BE4SAP2BEAYXABFZ/E4SSS0000014	5​	4.999972​	2​
P-12345-12000-01	4"	Equal Tee ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BTEEABMBEAYXABFZ/TEESS0000661	8​	4.12​	1​
P-12345-12000-01	4"	Cap ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BCAPABMBEAYXABFZ/CAPSS0000072	12​	5​	2​
P-12345-12000-01	3"	Cap ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BCAPABMBEAYXABFZ/CAPSS0000071	12​	2​	1​
P-12345-12000-02	4"x3"	Con. Reducer ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BRECABMBEAYXABFZ/RECSS0002440	10​	4​	1​
P-12345-12000-02	3"	Equal Tee ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BTEEABMBEAYXABFZ/TEESS0000665	8​	3.38​	1​
P-12345-12000-02	3"	Pipe ; ASME-B36.19M ; BE ; ASTM A312 Grade TP304/TP304L ; EFW (E = 0.80)	PPPABRBEAWCAAQZ/PP0SS0000878	1​	37.45269​	2​
P-12345-12000-02	3"	Cap ; ASME B16.9 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BCAPABMBEAYXABFZ/CAPSS0000071	12​	4​	2​
P-12345-12000-02	3"	90 Deg. Elbow 1D ; ASME B16.28 ; BE ; ASTM A403 Grade WP304/WP304L ; Type W	BE9SABBBEAYXABFZ/E9SSS0000615	2​	4.5​	1​

[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]

 

[jgarza0422]

Thanks, that worked for what I needed.

Thanks,
jarza0422

 

[sandy666]

you are welcome

Have a nice day