Forum Discussion
RobRayborn
5 months agoHelper IV
Power Query Add rows of zero amount
I have a need to add rows of data of a zero quantity. I have three columns PART, DATE, QTY. For each row add one month to the DUE DATE twelve times, so if the original data is Part A, 4/1/2026, 26,...
- 5 months ago
Here is a sample code that uses similar logic to your example.
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText("i45WCkgsKlFwVNJRMtE31DcyMDIDMoFErA5UygnIN0ZImZsrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Part = _t, Date = _t, Qty = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, { {"Part", type text}, {"Date", type date}, {"Qty", Int64.Type} } ), generate_records = Table.AddColumn( #"Changed Type", "next12Months", each List.Transform( {0..12}, (r)=> [ Part=[Part], Date=Date.AddMonths([Date], r), Qty=if r = 0 then [Qty] else 0 ] ), type list ), remove_columns = Table.RemoveColumns( generate_records, {"Part", "Date", "Qty"} ), expand_lists = Table.ExpandListColumn( remove_columns, "next12Months" ), expand_records = Table.ExpandRecordColumn( expand_lists, "next12Months", {"Part", "Date", "Qty"}, {"Part", "Date", "Qty"} ), set_types = Table.TransformColumnTypes( expand_records, { {"Part", type text}, {"Date", type date}, {"Qty", Int64.Type} } ) in set_typesHere I start with example table...
and end with...
RobRayborn
5 months agoHelper IV
Thank you so very much. You have helped to solve my issue of missing data.