Just to illustrate that this topic was much more complicated than suggested in the questions raised, these are the queries I created.
Query Oct Tracker to import the Excel sheet:
let
Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Tracker - Copy.xlsx"), null, true),
#"Oct Tracker_Sheet" = Source{[Item="Oct Tracker",Kind="Sheet"]}[Data],
#"Removed Blank Rows" = Table.SelectRows(#"Oct Tracker_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"
Query Targets with the targets for each name, replacing 0 by infinity:
let
Source = #"Oct Tracker",
#"Removed Other Columns" = Table.SelectColumns(Source,{"Column1", "Column3", "Column4"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Location/ Nsame", type text}, {"Target X", Int64.Type}, {"Target Z", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Target X] <> null and [Target Z] <> null)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([#"Location/ Nsame"], "Total")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Location/ Nsame", "Name"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",0,Number.PositiveInfinity,Replacer.ReplaceValue,{"Target X", "Target Z"})
in
#"Replaced Value"
Query Categories:
#table(type table[Sku = text, Category = text],
{{"Sku100", "X"},
{"Sku150", "X"},
{"Sku200", "Z"},
{"Sku60", "X"},
{"Sku250", "Z"},
{"Sku350", "Z"},
{"Sku750", "Z"},
{"Sku1000", "Z"},
{"Sku300", "Z"}})
Query Result:
let
Source = #"Oct Tracker",
#"Filtered Rows1" = Table.SelectRows(Source, each ([Column1] = null and [Column5] <> null) or not Text.StartsWith([Column1], "Location ")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows1"),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Location/ Nsame", "Day"}, {"Column2", "Sku"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Day] is number and [Sku] <> "Total"),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Day", "Sku"}, "Name", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns",{"Sku"},Categories,{"Sku"},"Categories",JoinKind.LeftOuter),
#"Expanded Categories" = Table.ExpandTableColumn(#"Merged Queries", "Categories", {"Category"}, {"Category"}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Expanded Categories",{{"Name", Order.Ascending}, {"Category", Order.Ascending}, {"Day", Order.Ascending}})),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Day", "Name", "Category"}, {{"Sku", each Text.Combine([Sku],", "), type text}, {"Value", each List.Sum([Value]), Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
#"Merged Queries1" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
#"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries1", "Previous", {"Name", "Category"}, {"Previous.Name", "Previous.Category"}),
#"Sorted Rows1" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows1", "StartIndex", each if [Name] <> [Previous.Name] or [Category] <> [Previous.Category] then [Index] else null),
#"Filled Down1" = Table.FillDown(#"Added Custom",{"StartIndex"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down1", "Cumulative", each List.Sum(List.Range(#"Filled Down1"[Value],[StartIndex],[Index.1] - [StartIndex])), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Index.1", "Previous.Name", "Previous.Category", "StartIndex"}),
#"Merged Queries2" = Table.NestedJoin(#"Removed Columns",{"Name"},Targets,{"Name"},"Targets",JoinKind.LeftOuter),
#"Expanded Targets" = Table.ExpandTableColumn(#"Merged Queries2", "Targets", {"Target X", "Target Z"}, {"Target X", "Target Z"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Targets", "Cumulative Over Target", each List.Max({0,[Cumulative] - (if [Category] = "X" then [Target X] else [Target Z])}), Int64.Type),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Single Value Over Target", each List.Min({[Value],[Cumulative Over Target]}), Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom3",Number.PositiveInfinity,0,Replacer.ReplaceValue,{"Target X", "Target Z"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Day", Int64.Type}})
in
#"Changed Type"
Specializing in Power Query Formula Language (M)
