That's amazing thank you.
I've been trying to replicate the second table 'Stock Turnover' from my pbix with my new found ability.
So Query3 becomes the new StocksSold Query;
let
Source = #"itemdetailsdogfood$",
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time - Copy", Int64.Type}, {"Item", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date", "Item", "Time - Copy"}),
#"Sorted Rows1" = Table.Sort(#"Removed Other Columns",{{"Time - Copy", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows1", {"Item"}, {{"All", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Group([All], {"Time - Copy"}, {{"Prev", each List.Max(_[Index])}})),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"All"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Time - Copy", "Prev"}, {"Time - Copy", "Prev"}),
#"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Share", each [Prev]/List.Count(List.Distinct(Source[Date]))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Prev"})
in
#"Removed Columns"Produces this: https://gyazo.com/845668570b2ed9b7a674af83a3925775
Instead of this: https://gyazo.com/74a4e9364bf89fe3ea62dfdc651608cb
My first instinct is to pivot the table.
Pivotting produces this: https://gyazo.com/da68b8bef4ddac201137fb78f384d453
But then when I do the powerquery for the final table;
let
Source = Csv.Document(File.Contents("C:\Users\Elliot Parker\Desktop\itemlist.csv"),[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Token", type text}, {"Item Name", type text}, {"Description", type text}, {"Category", type text}, {"SKU", type text}, {"Variation Name", type text}, {"Price", type number}, {"Current Quantity Neoscona", type text}, {"New Quantity Neoscona", type text}, {"Stock Alert Enabled Neoscona", type text}, {"Stock Alert Count Neoscona", type text}, {"Modifier Set - Bacon", type text}, {"Modifier Set - Coffee", type text}, {"Modifier Set - Banana", type text}, {"Tax - GST (10%)", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([#"Modifier Set - Bacon"], "Y") then "Bacon" else if Text.Contains([#"Modifier Set - Coffee"], "Y") then "Coffee" else if Text.Contains([#"Modifier Set - Banana"], "Y") then "Banana" else null ),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Stock Level", each if Text.Contains([Item Name], "Flat White") then "10" else if Text.Contains([Item Name], "Fritters") then "15" else if Text.Contains([Item Name], "Scrambled Eggs") then "15" else if Text.Contains([Item Name], "Smoothie") then "10" else null ),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Reorder Quantity", each if Text.Contains([Item Name], "Flat White") then "25" else if Text.Contains([Item Name], "Fritters") then "16" else if Text.Contains([Item Name], "Smoothie") then "11" else if Text.Contains([Item Name], "Scrambled Eggs") then "10" else null ),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column2",{{"Reorder Quantity", Int64.Type}, {"Stock Level", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Item Name", "Stock Level", "Reorder Quantity"}),
StocksSold = #"Query3",
#"Stocks Empty At" = Table.AddColumn(#"Changed Type","Stocks Empty At", (x) => if List.PositionOf(Table.Column(Query3,x[Item]),List.Min(List.Select(Table.Column(Query3,x[Item]),each _> x[Stock Level]))) > -1 then Query3[Time - Copy]{List.PositionOf(Table.Column(Query3,x[Item]),List.Min(List.Select(Table.Column(Query3,x[Item]),each _> x[Stock Level])))} else -1)
in
#"Stocks Empty At"It tells me that [Time - Copy] is an invalid identifier on the final line. It's the exact same name that is present in the query3 table; any thoughts?
