PowerQuery removing columns based on total value of a condition

[ruthhacche]

I have a nominal transaction listing. I want to remove those rows where they sum to nil based on criteria (I am trying to get rid of all those pesky data cleansing journals)

So IF Source is 3 AND narrative is identical THEN sum the value - and I shall remove all the zeroes. But I do not want to group as this is just a cleansing step not generating my output and I do not want to lose all the other data in this table. Any suggestions?

 

[ruthhacche]

So I have sorted this - concatenate a hash of the source, narrative and absolute value and remove duplicates. A botch and subject to errors but will do for now.

 

[ruthhacche]

Well that doesnt work cos it only gets rid of one side doesnt it. Still open to ideas.

 

[alansidman]

Using XL2BB upload a sample of your data. Then mock up a solution so we can understand exactly what your needs are.

 

[ruthhacche]

Well I have no idea if this is correct - I am sure you will say if not. Basically these lines are pairs that have the same nominal source, the same text and sum to zero - I want to remove the whole rows before i output to my report. NB there a lot of other columns in the report and lots more rows that do not match these criteria. If you need more then please let me know

NLNO	NLSOURCE	NLDATE	Reference	Narrative	CURRVAL	STGVAL
311023	3	31/08/2019 00:00	201908	0112.1208.71.09.OCL.OSZ: BATCH TRF: 201908	-56.09	-56.09
311023	3	31/08/2019 00:00	201908	0112.1208.71.09.OCL.OSZ: BATCH TRF: 201908	56.09	56.09
311023	3	31/08/2019 00:00	201908	0112.1208.71.09.OCL.OSZ: BATCH TRF: 201908	56.09	56.09
311023	3	31/08/2019 00:00	201908	0112.1208.71.09.OCL.OSZ: BATCH TRF: 201908	56.09	56.09
311023	3	31/08/2019 00:00	201908	0112.1208.71.09.OCL.OSZ: BATCH TRF: 201908	-56.09	-56.09
311023	3	31/08/2019 00:00	201908	0112.1208.71.09.OCL.OSZ: BATCH TRF: 201908	-56.09	-56.09

 

[sandy666]

try to post representative example AND expected result

 

[ruthhacche]

Sandy apologies. The expected result is an empty table. I want to remove all those lines. I will post more of the data tomorrow And maybe that makes it clearer.

 

[sandy666]

for your example you can try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TP = Table.Partition(Source,"CURRVAL",1,each _),
    C2T = Table.FromList(TP, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandTableColumn(C2T, "Column1", {"NLNO", "NLSOURCE", "NLDATE", "Reference", "Narrative", "CURRVAL", "STGVAL"}, {"NLNO", "NLSOURCE", "NLDATE", "Reference", "Narrative", "CURRVAL", "STGVAL"})
in
    Expand

but would be fine to see representative example and expected result

 

[ruthhacche]

A selection before (again not all columns) but gives an idea. Red boxes are duplicates and blue box sums to zero. In that case I want the rows removed to clean the data for reporting. Edit - colours dont show - last two rows text lines NLNam NLSOURCE Reference and CLASS duplicates and value sums to zero.

NLNAME	URN	NLSOURCE	NLDATE	Reference	CUST	STGVAL	nlcat	Class	SubClass	Detail
A7.3 CTD OUT DELVERY MAS	151643	2	12/10/2019 00:00	29153	M&S FSV	100	3011 CUSTOMER COSTS MAS	CML2	OUTBOUND	201910
A7.3 CTD STORE MAS	151643	2	12/10/2019 00:00	29153	M&S FSV	100	3011 CUSTOMER COSTS MAS	CML2	STORAGE	201910
A7.2 Marketing Contribution CORE	153902	1	30/10/2019 00:00	SII177088	WH Smith	100	3010 CUSTOMER COSTS CORE	WHSMITH WH	XMAS PROMOTIONS	201912
STOCK ADJUST TRF STD COST MARKETPLACES	167102	3	31/08/2019 00:00	201908		102.17	3012 CUSTOMER COSTS MARKETPLACES	0132.3046.72.04.OCL.OSZ	:BATCH TRF 201908
STOCK ADJUST TRF STD COST CORE	167469	3	31/08/2019 00:00	201908		-102.17	3010 CUSTOMER COSTS CORE	0132.3046.72.04.OCL.OSZ	:BATCH TRF 201908
STOCK ADJUST TRF STD COST CORE	167756	3	31/01/2020 00:00	202001		843.8	3010 CUSTOMER COSTS CORE	0112.1213.71.01.OCL.OSZ	BATCH TRF	202001
STOCK ADJUST TRF STD COST CORE	166345	3	31/01/2020 00:00	202001		-843.8	3010 CUSTOMER COSTS CORE	0112.1213.71.01.OCL.OSZ	BATCH TRF	202001

To leave this

NLNAME	URN	NLSOURCE	NLDATE	Reference	CUST	STGVAL	nlcat	Class	SubClass	Detail
A7.3 CTD OUT DELVERY MAS	151643	2	12/10/2019 00:00	29153	M&S FSV	100	3011 CUSTOMER COSTS MAS	CML2	OUTBOUND	201910
A7.3 CTD STORE MAS	151643	2	12/10/2019 00:00	29153	M&S FSV	100	3011 CUSTOMER COSTS MAS	CML2	STORAGE	201910
A7.2 Marketing Contribution CORE	153902	1	30/10/2019 00:00	SII177088	WH Smith	100	3010 CUSTOMER COSTS CORE	WHSMITH WH	XMAS PROMOTIONS	201912
STOCK ADJUST TRF STD COST MARKETPLACES	167102	3	31/08/2019 00:00	201908		102.17	3012 CUSTOMER COSTS MARKETPLACES	0132.3046.72.04.OCL.OSZ	:BATCH TRF 201908
STOCK ADJUST TRF STD COST CORE	167469	3	31/08/2019 00:00	201908		-102.17	3010 CUSTOMER COSTS CORE	0132.3046.72.04.OCL.OSZ	:BATCH TRF 201908

This is the query I am working with and this cleaning needs to happen as the next step

let
Source = Sql.Databases("meld06"),
Sage200_HRL = Source{[Name="Sage200_HRL"]}[Data],
dbo_aaaNLquery = Sage200_HRL{[Schema="dbo",Item="aaaNLquery"]}[Data],
#"SELECT CAT CODES" = Table.SelectRows(dbo_aaaNLquery, each ([nlcat] = "3010 CUSTOMER COSTS CORE" or [nlcat] = "3011 CUSTOMER COSTS MAS" or [nlcat] = "3012 CUSTOMER COSTS MARKETPLACES" or [nlcat] = "3013 CUSTOMER COSTS US" or [nlcat] = "3014 CUSTOMER COSTS ONLINE")),
#"Create working narrative" = Table.DuplicateColumn(#"SELECT CAT CODES", "Narrative", "Narrative - Copy"),
#"Split Narrative" = Table.SplitColumn(#"Create working narrative", "Narrative - Copy", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Narrative - Copy.1", "Narrative - Copy.2", "Narrative - Copy.3", "Narrative - Copy.4"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Narrative",{{"Narrative - Copy.1", type text}, {"Narrative - Copy.2", type text}, {"Narrative - Copy.3", type text}, {"Narrative - Copy.4", type text}}),
#"Create SL narrative" = Table.AddColumn(#"Changed Type", "SLNarrative", each if [NLSOURCE] = 7 then [Narrative] else null),
#"Split SL Narrative" = Table.SplitColumn(#"Create SL narrative", "SLNarrative", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"SLNarrative.1", "SLNarrative.2", "SLNarrative.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split SL Narrative",{{"SLNarrative.1", type text}, {"SLNarrative.2", type text}, {"SLNarrative.3", Int64.Type}}),
#"create Narrative1" = Table.AddColumn(#"Changed Type1", "ClassTEMP", each if [NLSOURCE] = 7 then [SLNarrative.1] else [#"Narrative - Copy.1"]),
#"Create Narrative2" = Table.AddColumn(#"create Narrative1", "SubClass", each if [NLSOURCE] = 7 then [SLNarrative.2] else [#"Narrative - Copy.2"]),
#"Create Narrative 4" = Table.AddColumn(#"Create Narrative2", "Detail", each if [NLSOURCE] = 7 then [SLNarrative.3] else [#"Narrative - Copy.3"]),
#"Create narrative 4" = Table.RenameColumns(#"Create Narrative 4",{{"Narrative - Copy.4", "AdditionalDetail"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Create narrative 4",{{"ClassTEMP", type text}, {"SubClass", type text}, {"Detail", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"NLNO", "NLNAME", "URN", "NLTYPE", "NLSOURCE", "NLDATE", "Reference", "Narrative", "CURRVAL", "STGVAL", "CompanyName", "PeriodNumber", "AccountCostCentre", "AccountDepartment", "SecondReference", "AccountNumber", "Year", "id", "AllowJournalsToBePosted", "nlcat", "catcode", "cat", "AnalysisCode1", "Narrative - Copy.1", "Narrative - Copy.2", "Narrative - Copy.3", "SLNarrative.1", "SLNarrative.2", "SLNarrative.3", "ClassTEMP", "SubClass", "Detail", "AdditionalDetail"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Narrative - Copy.1", "Narrative - Copy.2", "Narrative - Copy.3", "SLNarrative.1", "SLNarrative.2", "SLNarrative.3"}),
#"INSERT SFC Account" = Table.FuzzyNestedJoin(#"Removed Columns", {"SubClass"}, SFCAccount, {"Sage_Account_Number__c"}, "SFCAccount", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, NumberOfMatches=1]),
#"Expanded SFCAccount2" = Table.ExpandTableColumn(#"INSERT SFC Account", "SFCAccount", {"Sage_Account_Number__c", "Pimra_Account_Number__c"}, {"Sage_Account_Number__c", "Pimra_Account_Number__c"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded SFCAccount2", "Class", each if [NLSOURCE] = 7 then [Pimra_Account_Number__c] else [ClassTEMP]),
#"INSERT pimra customer details" = Table.NestedJoin(#"Added Conditional Column", {"Class"}, TblPimraCustomers, {"PimraCustomer"}, "TblPimraCustomers", JoinKind.LeftOuter),
#"Expanded TblPimraCustomers" = Table.ExpandTableColumn(#"INSERT pimra customer details", "TblPimraCustomers", {"PimraCustomer", "PimraCustomerGroup", "PimraWarehouse", "PimraCustomerSubGroup", "BudgetGroup"}, {"PimraCustomer", "PimraCustomerGroup", "PimraWarehouse", "PimraCustomerSubGroup", "BudgetGroup"})
in
#"Expanded TblPimraCustomers"

But I will try the response above and let you know. A million thanks for your time.

 

[sandy666]

try to use [CODE]your code here[/CODE]
I'll try to decode your M from the mess above without source which I don't have
but I have to say without source I can do almost nothing