Forum Discussion
Excel urgent help
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"
Do you mean that you want the first Sku for which the target was exceeded?
So:
Sku2
null
Sku4
Sku4
Sku4
I thought it was urgent, so why no feedback yet?
Anyhow, assuming I guessed right, it can be done with this query:
let
// Input from another query Table1, in which the Excel table is imported into Power Query:
Source = Table1,
// Names of columns starting with "Sku", so this will be dynamic:
RequiredColumns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Sku")),
// Adding a column with nested lists, containing the values from the "sku*" columns:
#"Added Values" = Table.AddColumn(Source, "Values", each Record.FieldValues(Record.SelectFields(_,RequiredColumns))),
// Transforming the nested lists with values in accumulated values:
Accumulated = Table.TransformColumns(#"Added Values",{{"Values", each List.Accumulate(List.Skip(_), {_{0}}, (Cumulation,Value) => Cumulation & {List.Sum({List.Last(Cumulation),Value})})}}),
// Transforming the nested lists into a list of lists, each with the name of the Sku and the target minus the accumulated values.
// So this looks like for the first row: {{"Sku1", 4}, {"Sku2", -1}, etcetera}
#"Added SkuAndValues" = Table.AddColumn(Accumulated, "Overachieved Sku", (ThisRow) => List.Zip({RequiredColumns,List.Transform(ThisRow[Values], each ThisRow[Target] - _)})),
// Selecting the first Sku with negative value (i.e. Target exceeded); default value is null:
#"Selected First Sku" = Table.TransformColumns(#"Added SkuAndValues",{{"Overachieved Sku", each List.First(List.Select(_, each _{1} < 0),{null}){0}, type text}}),
// Removing temporary column:
#"Removed Columns" = Table.RemoveColumns(#"Selected First Sku",{"Values"})
in
#"Removed Columns"Hi Dear,
Sorry for the late reply but im not feeling very well.
what i mean is company for example has 7 sku devided to 2 categories smart and none smart, person has target of the 2 categories and when the person achieve 100% of his target i need to calculate his commission based in different price per sku therefore what i need the formula for is to give me how many of each sku over ahcieved after the 100%.
i hope i was able to explain it to you and thanks a lot in advance.
Hi. sorry you are not feeling well, this time of the year.
I would be glad to help you, but I really don't understand your requirements.
Also an additional requirement seems to have popped up: you didn't mention categories in your initial post.
Moreover: in your initial question you mentioned that you need the Sku which is overachieved; in your latest post you want "how many of each sku over achieved after the 100%", which is clearly a different question.
Can you elaborate an example, e.g. what output would you expect for Abdallah and why?
Initially I thought it would be Sku2, as Sku1 has 11 units, next would be Sku2 with 5 units, so if Sku2 comes after Sku1, Sku2 would be the Sku that exceeds the target of 15, as 11 + 5 = 16.
https://drive.google.com/file/d/1h0kEjWyBVp16CVsK4RxJggxM1X2tCja7/view?usp=sharing
Hi, Thanks for the spirit and merry christmas to you.
I have attached the sheet im working on, i hope when you take a look into it you will be able to understand what exactly i want to do.
regards
