Forum Discussion
Solved
Create extra rows based on amount but only keep some fields
How to get from table 1 to table 2? My main struggle is with creating those extra rows, but not filling the dates for the products that don't have a date yet. Example: I have table1, I want to get ...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYlMgVorViVYyAjKcgBhEG+oa6RoZGBlgSBghSxgDBcJAqsE6jBESJkABF6jZhrom2CWMcEkYI0uABFzhrjJFSJgBBdzglpshJMyBAu5QHWABCyDDA2QuWCAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, product = _t, amount = _t, date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"product", type text}, {"amount", Int64.Type}, {"date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"id", "product", "amount"}, {{"Date2", each [date], type table [date=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date", each Table.FromColumns( {List.Combine({ [Date2], List.Repeat({null},[amount]-List.Count([Date2]))}) , List.Numbers(1,[amount])}, {"Date", "Index"})), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date2"}), #"Expanded Date" = Table.ExpandTableColumn(#"Removed Columns", "Date", {"Date", "Index"}, {"Date.1", "Index"}) in #"Expanded Date"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
I have table1, I want to get table 2 with the extra data (red).
If a product has amount 5, for example product D, and it only has 3 rows with dates, it means only 3 out of 5 have a date. I need to get a table with all 5 rows, but with only the 3 dates filled for the first 3 in the series: 1, 2 and 3. The remaining 4 and 5 should have no date: null.
I hope it's more clear now?
