Forum Discussion
RobRayborn
4 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,...
- 4 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...
jgeddes
4 months agoSuper User
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_types
Here I start with example table...
and end with...