Forum Discussion
dates calculation and transpose / pivot table
Hello all, Hope you are doing amazingly well. I have a stock out (fact) table (table 1) that shows the product , stock out start date, stock out end date, stock out value (among other attrib...
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLjFU0lEy1PUqzdM1MgIyjQ0QbFMDpVgduEKQiKGprmNpOkQWyPZPLoGwjVCVGoOEDHT98sugSoGGJgINNQZbAFQaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Stock out Start Date" = _t, #"Stock out End Date" = _t, #"Stock out Value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Stock out Start Date", type date}, {"Stock out End Date", type date}, {"Stock out Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Stock out Duration", each Duration.Days([Stock out End Date]-[Stock out Start Date])+1), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Daily Value", each Number.Round([Stock out Value]/[Stock out Duration],2)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Transform(List.Dates([Stock out Start Date],[Stock out Duration],#duration(1,0,0,0)),each Date.ToText(_,"MMM/yyyy"))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"), #"Grouped Rows" = Table.Group(#"Expanded Custom", {"Product", "Stock out Start Date", "Stock out End Date", "Stock out Value", "Stock out Duration", "Daily Value", "Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Total", each Number.Round([Count]*[Stock out Value]/[Stock out Duration],2)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Count"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Total", List.Sum) in #"Pivoted Column"
Hello Vijay_A_Verma ,
I think I am now able to modify your code to meet my real file's requirements.
Before flagging your reply as the solution, can you please explain why you have to use "each" at your code? From the moment you create a new colum, I would expect the code to be processed at each row, hence I don't understand why your code works only if you use "each".
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Transform(List.Dates([Stock out Start Date],[Stock out Duration],#duration(1,0,0,0)),each Date.ToText(_,"MMM/yyyy"))),
Thank you in advance,
George
For Table.AddColumn - each means this operation should be done for all cells one by one.
For List.Transform - each means this operation should be done for all elements one by one