Remove columns from output if their total is 0

[JackDanIce]

Hi,

I have a PQ process that takes an input table, removes some columns, reorders them, then produces an output table:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Input"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"NSV Estimate", "RPC","Total Retro", "Total", "NNSV / Case", "% TTS", "Investment", "% Investment"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Brand", "SKU", "Stocking Policy", "Stocking Policy Description", "Volume", "Retro", "Lump Sum", "A&P"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Brand", type text}, {"SKU", type text}, {"Stocking Policy", type text}, {"Stocking Policy Description", type text}, {"Volume", Int64.Type}, {"Retro", Currency.Type}, {"Lump Sum", Currency.Type}, {"A&P", Currency.Type}})
in
    #"Changed Type"

I would like to exclude columns Lump Sum and A&P (the last two columns in the table), if the column total = 0, their values will always be non-negative.

How can this be done please?

TIA,
Jack

 

[JGordon11]

Book2
	A	B	C	D	E	F	G
1	A	B	C	D		A	B
2	4	7	0	0		4	7
3	2	0	0	0		2	0
4	3	2	0	0		3	2
5	9	4	0	0		9	4
6	9	9	0	0		9	9
7	9	3	0	0		9	3
8	2	7	0	0		2	7
9	1	1	0	0		1	1
10	2	5	0	0		2	5
11	10	2	0	0		10	2
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"D", type number}, {"C", type number}, {"B", type number}, {"A", type number}}),
    CSum = List.Sum(Table.Column(ChangedType, "C")),
    DSum = List.Sum(Table.Column(ChangedType, "D")),
    CRemove = if (CSum = 0) then Table.RemoveColumns(ChangedType,{"C"}) else ChangedType,
    DRemove = if (DSum = 0) then Table.RemoveColumns(CRemove,{"D"}) else CRemove

in
    DRemove

Book2
	A	B	C	D	E	F	G	H
1	A	B	C	D		A	B	D
2	4	7	0	0		4	7	0
3	2	0	0	0		2	0	0
4	3	2	1	3		3	2	3
5	9	4	0	4		9	4	4
6	9	9	-1	5		9	9	5
7	9	3	0	0		9	3	0
8	2	7	0	0		2	7	0
9	1	1	0	0		1	1	0
10	2	5	0	0		2	5	0
11	10	2	0	0		10	2	0
Sheet1

 

[JGordon11]

In your code

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Input"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"NSV Estimate", "RPC","Total Retro", "Total", "NNSV / Case", "% TTS", "Investment", "% Investment"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Brand", "SKU", "Stocking Policy", "Stocking Policy Description", "Volume", "Retro", "Lump Sum", "A&P"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Brand", type text}, {"SKU", type text}, {"Stocking Policy", type text}, {"Stocking Policy Description", type text}, {"Volume", Int64.Type}, {"Retro", Currency.Type}, {"Lump Sum", Currency.Type}, {"A&P", Currency.Type}}),
    LumpSumSum = List.Sum(Table.Column(#"Changed Type", "Lump Sum")),
    APSum = List.Sum(Table.Column(#"Changed Type", "A&P")),
    LSRemove = if (LumpSumSum = 0) then Table.RemoveColumns(#"Changed Type",{"Lump Sum"}) else #"Changed Type",
    APRemove = if (APSum = 0) then Table.RemoveColumns(LSRemove,{"A&P"}) else LSRemove
in
    APRemove

may have a few typos because I can't test it without your spreadsheet

 

[JackDanIce]

Hi @JGordon11 thank you for the suggestion, I'll modify it accordingly but I can see what you've tried to do and how it's meant to work.
I'll mark this solution as complete after I've had a chance to test, but this looks like the solution I was hoping for - thank you!

 

[JackDanIce]

Hi @JGordon11

Needed to replace nulls with 0 and then it worked, thank you very much and Happy Easter!

Final code (changed order of some of the other steps whilst trying to get it to work):

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Input"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"NSV Estimate", "RPC","Total Retro", "Total", "NNSV / Case", "% TTS", "Investment", "% Investment"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Brand", type text}, {"SKU", type text}, {"Stocking Policy", type text}, {"Stocking Policy Description", type text}, {"Volume", Int64.Type}, {"Retro", Currency.Type}, {"Lump Sum", Currency.Type}, {"A&P", Currency.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Brand", "SKU", "Stocking Policy", "Stocking Policy Description", "Volume", "Retro", "Lump Sum", "A&P"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,0,Replacer.ReplaceValue,{"Lump Sum", "A&P"}),
    LumpSumSum = List.Sum(Table.Column(#"Replaced Value", "Lump Sum")),
    APSum = List.Sum(Table.Column(#"Replaced Value", "A&P")),
    LSRemove = if (LumpSumSum = 0) then Table.RemoveColumns(#"Replaced Value",{"Lump Sum"}) else #"Replaced Value",
    APRemove = if (APSum = 0) then Table.RemoveColumns(LSRemove,{"A&P"}) else LSRemove
in
    APRemove

 

[shaowu459]

= Table.PromoteHeaders(Table.FromColumns(List.Select(Table.ToColumns(Table.DemoteHeaders(Source)),each List.Sum(List.Skip(_))<>0)))

 

[JackDanIce]

Thank you @shaowu459, I think I understand what you're suggesting, but I only need to test two columns "Lump Sum" and "A&P" - or I have implemented your suggestion incorrectly.

This is what I have:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Input"]}[Content],
    /*#"Removed Columns" = Table.RemoveColumns(Source,{"NSV Estimate", "RPC","Total Retro", "Total", "NNSV / Case", "% TTS", "Investment", "% Investment"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Brand", type text}, {"SKU", type text}, {"Stocking Policy", type text}, {"Stocking Policy Description", type text}, {"Volume", Int64.Type}, {"Retro", Currency.Type}, {"Lump Sum", Currency.Type}, {"A&P", Currency.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Brand", "SKU", "Stocking Policy", "Stocking Policy Description", "Volume", "Retro", "Lump Sum", "A&P"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,0,Replacer.ReplaceValue,{"Lump Sum", "A&P"}),*/
    #"Output" = Table.PromoteHeaders(Table.FromColumns(List.Select(Table.ToColumns(Table.DemoteHeaders(Source)),each List.Sum(List.Skip(_))<>0)))
    /*#"Step 6" = Table.FromColumns(List.Select(Table.ToColumns(#"Replaced Value"), each List.Sum(List.Skip(_)<>0)))
    LumpSumSum = List.Sum(Table.Column(#"Replaced Value", "Lump Sum")),
    APSum = List.Sum(Table.Column(#"Replaced Value", "A&P")),
    LSRemove = if (LumpSumSum = 0) then Table.RemoveColumns(#"Replaced Value",{"Lump Sum"}) else #"Replaced Value",
    APRemove = if (APSum = 0) then Table.RemoveColumns(LSRemove,{"A&P"}) else LSRemove*/
in
    /*APRemove*/
    #"Output"

However, I get an expression error: We cannot apply operator - to types Text and Text. I believe this is because it's testing a non numerical column.

I tried to change Step 6 to have a list of {"Lump Sum", "A&P"} but I must have had the syntax wrong as I couldn't get it to work.

 

[shaowu459]

Hi, if you only have two specific columns to test, JGordon11's solution is a good choice. I made my code using the sample data in post #2, so my solution may be not suitable to you source data.

 

[JackDanIce]

Thanks for the clarification yes I thought it was looping over all the columns but appreciate posting as given me something else to learn.

Condescend it a bit more, for completeness, final code:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Input"]}[Content],
    #"Remove Columns" = Table.RemoveColumns(Source,{"NSV Estimate", "RPC","Total Retro", "Total", "NNSV / Case", "% TTS", "Investment", "% Investment"}),
    #"Force Data Type" = Table.TransformColumnTypes(#"Remove Columns",{{"Brand", type text}, {"SKU", type text}, {"Stocking Policy", type text}, {"Stocking Policy Description", type text}, {"Volume", Int64.Type}, {"Retro", Currency.Type}, {"Lump Sum", Currency.Type}, {"A&P", Currency.Type}}),    
    #"Replace null values" = Table.ReplaceValue(Table.ReorderColumns(#"Force Data Type",{"Brand", "SKU", "Stocking Policy", "Stocking Policy Description", "Volume", "Retro", "Lump Sum", "A&P"}),null,0,Replacer.ReplaceValue,{"Lump Sum", "A&P"}),        
    #"Lump Zero Sum Remove" = if (List.Sum(Table.Column(#"Replace null values", "Lump Sum")) = 0) then Table.RemoveColumns(#"Replace null values",{"Lump Sum"}) else #"Replace null values",
    #"AP Zero Sum Remove" = if (List.Sum(Table.Column(#"Replace null values", "A&P")) = 0) then Table.RemoveColumns(#"Lump Zero Sum Remove",{"A&P"}) else #"Lump Zero Sum Remove",
    Output = #"AP Zero Sum Remove"
in
    Output