Challenged to replace IF INDEX MATCH

[15266]

Hi everyone,

Hoping someone could help me with the following conundrum I'm wrecking my head around.
I have a query wich is a filtered download of a large data file, containing for each Product_factory combination info about component types annd quantities going "in" and "out" of the production process. The sum of "in" and "out" are not necessarily equal for each product due to production losses.

My goal is the following: to find the sum of all Productx "out" that contain Component A "in". In below example 310.
Is it possible?

Endproduct_factory	Components	Quantity	In/Out
Product1_factory1	A	-40	in
Product1_factory1	B	-200	in
Product1_factory1	C	-60	in
Product1_factory1	Product1	310	out
Product2_factory1	B	-100	in
Product2_factory1	C	-100	in
Product2_factory1	Product2	205	out

 

[pjmorris]

the challenge is because you have mixed components and products in the same column. A product is not a component - I don't even think you could do this with index match. Assuming you're looking at this in powerquery I would do a conditional column that copied only 'components' starting with Product to a new column and then 'Fill Up'. You would then have components s A, B & C listed against Product 1, etc That should give you something to work with.

HTH

 

[15266]

Hi Peter,

Ok thanks, I've tried a few things and got to the below, but would you know how I can get them to be aligned to one line i.e. remove the empties per same Endproduct_factory?


Current

Endproduct_factory	Component A	Component B	Component C	Product1
Product1_factory1	-40
Product1_factory1		-200
Product1_factory1			-60
Product1_factory1				310

To-be

Endproduct_factory	Component A	Component B	Component C	Product1
Product1_factory1	-40	-200	-60	310

 

[pjmorris]

I've used PowerQuery and the easiest way for you to replicate this is by going to the PowerQuert Advanced Editor and copying the following code: It assumes that your data above is in a table called Table1. It works by:
1. Delete the In/Out column
2. A couple of lines extract the Product and Factory from the EndProduct_factory field - this could be omitted if you wish, but I think it will give your more flexibility later.
3. Changes any components called Product1, Product2 etc to 'Product Count' as this will form the new column name.
4. Unpivots the Item and Quantity columns.
The result should be much as you have above.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Endproduct_factory", type text}, {"Components", type text}, {"Quantity", Int64.Type}, {"In/Out", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"In/Out"}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Columns", "Text Before Delimiter", each Text.BeforeDelimiter([Endproduct_factory], "_"), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Before Delimiter",{{"Text Before Delimiter", "Product"}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Renamed Columns", "Text After Delimiter", each Text.AfterDelimiter([Endproduct_factory], "_"), type text),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "Factory"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Endproduct_factory", "Factory", "Product", "Components", "Quantity"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Item", each if Text.StartsWith([Components], "Prod") then "Product Count" else [Components]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Components"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Endproduct_factory", "Factory", "Product", "Item", "Quantity"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"[Item]), "Item", "Quantity", List.Sum)
in
    #"Pivoted Column"

____________________________________________________________

However, I'm still not sure that that will be your best structure. The code at the bottom of this post (again, it should be copied into the Advanced Editor in Powerquery), will produce this table:

Endproduct_factory	Products	Components	Input	Output
Product1_factory1	Product1	A	-40	310
Product1_factory1	Product1	B	-200	310
Product1_factory1	Product1	C	-60	310
Product2_factory1	Product2	B	-100	205
Product2_factory1	Product2	C	-100	205

This is the code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Endproduct_factory", type text}, {"Components", type text}, {"Quantity", Int64.Type}, {"In/Out", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Products", each if Text.StartsWith([Components], "Prod") then [Components] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Products"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Up", "Output", each if Text.StartsWith([Components], "Prod") then [Quantity] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Input", each if [Quantity] < 0 then [Quantity] else 0),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column2",{"Endproduct_factory", "Components", "Quantity", "In/Out", "Products", "Input", "Output"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Input", type number}, {"Output", type number}}),
    #"Filled Up1" = Table.FillUp(#"Changed Type1",{"Output"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up1", each not Text.StartsWith([Components], "Prod")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Quantity", "In/Out"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Endproduct_factory", "Products", "Components", "Input", "Output"})
in
    #"Reordered Columns1"

HTH

 

[citizenbh]

A little shorter:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Endproduct_factory", type text}, {"Component A", Int64.Type}, {"Component B", Int64.Type}, {"Component C", Int64.Type}, {"Product1", Int64.Type}}),
    #"Filled Up" = Table.FillUp(#"Changed Type",{"Product1"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Up", {"Product1", "Endproduct_factory"}, "Attribute", "Value"),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Text Before Delimiter", each Text.BeforeDelimiter([Endproduct_factory], "_"), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Before Delimiter",{{"Text Before Delimiter", "Product"}, {"Product1", "Output"}, {"Value", "Input"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Endproduct_factory", "Product", "Attribute", "Input", "Output"})
in
    #"Reordered Columns"

 

[pjmorris]

Hi Citizen, that works if you start with the table in post #3, you need the additional steps when you start at #1. That said you well make the point that there are always alternative solutions - its what I like about Excel.

 

[citizenbh]

The model is set incorrectly from the beginning.