Hello,
thanks to everyone who participated! I’d say for this tricky workout everyone who tried it is a winner.
If you are looking for a super short solution, then check out @borydobon. I will as ever try to spill out an accessible step by step solution. I just use two advanced elements: GroupKind.Local in a grouping step plus a one-step shifting technique.
Let’s start off on the Add Column tab with a Custom Column:
We call it ID and use this formula to build an ID:
if [Qty] <0 then Text.From([ID_Product]) & Text.From([Qty]) else Text.From([ID_Product]) &“-”& Text.From([Qty])
Then we sort the ID column and Date column ascending:
= Table.Sort(#“Added ID”,{{“ID”, Order.Ascending}, {“Date”, Order.Ascending}})
Next, we mark first the ID column and then the Qty column and select on the Home tab the Group By button. Alternatively make a right mouse click and select Group By:
As New Column name take e.g. All and as Operation take All Rows:
In the formula bar add , GroupKind.Local at the end:
This will make sure that we keep all sales and return sequences and do not group them together.
Now let’s add another Custom Column called “Substract” with this simple formula:
if [Qty] < 0 then Table.RowCount([All]) else 0
Let’s try to shift this up by one row. We could add a 0 based Index and a 1 based index and then merge the table with itself. Instead, we use a more advanced one step shifting technique from Imke Feldmann.
By pressing on fx left of the formula bar we add a custom step, and we add this formula:
= Table.FromColumns(
Table.ToColumns(#“Added Substract”) & {List.Skip(#“Added Substract”[Substract]) & {0}},
Table.ColumnNames(#“Added Substract”) & {“SubstractShifted”} )
We add a third Custom Column with this formula:
if [Substract]+[SubstractShifted] = 0 then [All] else Table.RemoveLastN([All], [Substract]+[SubstractShifted])
The formula will make sure that we remove the number of returns from the end of the sales tables (taking out the last sales lines) and also from the return tables (making them empty).
As the table of the Custom column includes all relevant columns we write at the end of the formula [[Custom]] to only keep the Custom column:
Let’s click on the expand symbol on the top right and deselect ID and there is no prefix needed:
Click on the top right triangle of any of the columns and deselect null to filter out all empty rows:
Finally mark the ID_Sales column and sort it ascending:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Added ID" = Table.AddColumn(Source, "ID", each if [Qty] <0 then Text.From([ID_Product])& Text.From([Qty]) else Text.From([ID_Product])&"-"&Text.From([Qty])),
#"Sorted Rows" = Table.Sort(#"Added ID",{{"ID", Order.Ascending}, {"Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Qty", "ID"}, {{"All", each _, type table [ID_Sales=number, Date=datetime, ID_Product=number, Product=text, Qty=number, Unit Value=number, Value Total=number, ID=text]}}, GroupKind.Local),
#"Added Substract" = Table.AddColumn(#"Grouped Rows", "Substract", each if [Qty] < 0 then Table.RowCount([All]) else 0),
Shifted = Table.FromColumns(
Table.ToColumns(#"Added Substract") & {List.Skip(#"Added Substract"[Substract]) & {0}},
Table.ColumnNames(#"Added Substract") & {"SubstractShifted"} ),
#"Added Custom" = Table.AddColumn(Shifted, "Custom", each if [Substract]+[SubstractShifted] = 0 then [All] else Table.RemoveLastN([All], [Substract]+[SubstractShifted]))[[Custom]],
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID_Sales", "Date", "ID_Product", "Product", "Qty", "Unit Value", "Value Total"}, {"ID_Sales", "Date", "ID_Product", "Product", "Qty", "Unit Value", "Value Total"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([ID_Sales] <> null)),
#"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"ID_Sales", Order.Ascending}})
in
#"Sorted Rows1"
Could it be done shorter? Yes, but for a great deal, this step-by-step approach is UI supported and most formulas should be relatively easily understandable.
The one-step shifting technique is advanced but very useful and you can learn it as a pattern. In any case you can also use the more beginner friendly merge against itself technique with two index columns as alternative.
Try it out and make sure to check the other approaches above! => Hope you learn something!
Big thanks to everyone who has blurred or hidden the details! I have taken this now out so that it’s easier to compare solutions.
