Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Guys,
I have Table_sales_Order which holds all the sales order quantities with due dates and Table_Inventory_MSTR holds On_hand_qty. I am trying to create a scheduling report that takes out on hand quantity from the first day first and if there is any quantity left that should be taken out from the second day and so on.
Here is the sample I want to achieve
Current values
On hand Qty | Part# | 08/12 | 08/13 | 08/14 | 08/15 | 08/16
30 | ABC | 10 | 10 | 10 | 10 | 10 (Quantity on sales orders)
40 | XYZ | 5 | 20 | 10 | 15 | 20
Want to Achieve
Part# | 08/12 | 08/13 | 08/14 | 08/15 | 08/16
ABC | 0 | 0 | 0 | 10 | 10
XYZ | 0 | 0 | 0 | 10 | 20
Please let me know if you need further information.
Solved! Go to Solution.
Hi @mukhtarali011,
it will be a little bit complicated, but every rows of the code is commented. Enjoy 🙂
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s9TyEjMS1EILKlUUKhRUAhILCpRBjIMLPQNjaC0MZQ2gdKmUNpMKVYnWsnYQAECgLodnZwhDCAwNEBmERIEA7B5JkjmRURGwVUpmCrA2UZYTTSFKzUyUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Column1"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"OnhandQty", Int64.Type}, {"Part#", type text}, {"08/12", Int64.Type}, {"08/13", Int64.Type}, {"08/14", Int64.Type}, {"08/15", Int64.Type}, {"08/16", Int64.Type}}), // till now only data preparation HereStartsToCode = #"Changed Type2", // convert table to list of records TableToRecordList = Table.ToRecords(HereStartsToCode), // transform every record Result = List.Transform( TableToRecordList, (record) => let // get names of all columns containing slash ColumnNames = List.Select(Record.FieldNames(record), each Text.Contains(_, "/")), // get initial quantity Quantity = record[OnhandQty], // create a new record Acc = List.Accumulate( // go through list ColumnNames, // initial state [LeftQuantity = Quantity, Result = []], // do for each (state, current) => let // calculate new result value according to last left quantity left = Record.Field(record, current) - state[LeftQuantity], // create a new state depending on the left value result = if left < 0 then [LeftQuantity = -left, Result = state[Result] & Expression.Evaluate("[#""" & current & """ = 0]")] else [LeftQuantity = 0, Result = state[Result] & Expression.Evaluate("[#""" & current & """ = left]", [left = left])] in result ), // combine old record with the new one ResultRecord = record & Acc[Result] in ResultRecord ), // convert list back to table ConvertToTable = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // expand records into columns ExpandRecord = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"OnhandQty", "Part#", "08/12", "08/13", "08/14", "08/15", "08/16"}) in ExpandRecord
Hi @mukhtarali011,
it will be a little bit complicated, but every rows of the code is commented. Enjoy 🙂
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s9TyEjMS1EILKlUUKhRUAhILCpRBjIMLPQNjaC0MZQ2gdKmUNpMKVYnWsnYQAECgLodnZwhDCAwNEBmERIEA7B5JkjmRURGwVUpmCrA2UZYTTSFKzUyUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Column1"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"OnhandQty", Int64.Type}, {"Part#", type text}, {"08/12", Int64.Type}, {"08/13", Int64.Type}, {"08/14", Int64.Type}, {"08/15", Int64.Type}, {"08/16", Int64.Type}}), // till now only data preparation HereStartsToCode = #"Changed Type2", // convert table to list of records TableToRecordList = Table.ToRecords(HereStartsToCode), // transform every record Result = List.Transform( TableToRecordList, (record) => let // get names of all columns containing slash ColumnNames = List.Select(Record.FieldNames(record), each Text.Contains(_, "/")), // get initial quantity Quantity = record[OnhandQty], // create a new record Acc = List.Accumulate( // go through list ColumnNames, // initial state [LeftQuantity = Quantity, Result = []], // do for each (state, current) => let // calculate new result value according to last left quantity left = Record.Field(record, current) - state[LeftQuantity], // create a new state depending on the left value result = if left < 0 then [LeftQuantity = -left, Result = state[Result] & Expression.Evaluate("[#""" & current & """ = 0]")] else [LeftQuantity = 0, Result = state[Result] & Expression.Evaluate("[#""" & current & """ = left]", [left = left])] in result ), // combine old record with the new one ResultRecord = record & Acc[Result] in ResultRecord ), // convert list back to table ConvertToTable = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // expand records into columns ExpandRecord = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"OnhandQty", "Part#", "08/12", "08/13", "08/14", "08/15", "08/16"}) in ExpandRecord
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.