Forum Discussion
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 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.
table 1
| id | product | amount | date |
| 1 | A | 5 | null |
| 2 | B | 2 | 1-2-2020 |
| 2 | B | 2 | 2-2-2020 |
| 3 | V | 1 | 1-3-2020 |
| 4 | D | 5 | 1-4-2020 |
| 4 | D | 5 | 2-4-2020 |
| 4 | D | 5 | 3-4-2020 |
| 5 | E | 2 | 1-5-2020 |
| 6 | F | 1 | 1-6-2020 |
| 7 | G | 2 | null |
| 8 | H | 3 | null |
table 2
| id | product | amount | date | index |
| 1 | A | 5 | null | 1 |
| 1 | A | 5 | null | 2 |
| 1 | A | 5 | null | 3 |
| 1 | A | 5 | null | 4 |
| 1 | A | 5 | null | 5 |
| 2 | B | 2 | 1-2-2020 | 1 |
| 2 | B | 2 | 2-2-2020 | 2 |
| 3 | V | 1 | 1-3-2020 | 1 |
| 4 | D | 5 | 1-4-2020 | 1 |
| 4 | D | 5 | 2-4-2020 | 2 |
| 4 | D | 5 | 3-4-2020 | 3 |
| 4 | D | 5 | null | 4 |
| 4 | D | 5 | null | 5 |
| 5 | E | 2 | 1-5-2020 | 1 |
| 5 | E | 2 | null | 2 |
| 6 | F | 1 | 1-6-2020 | 1 |
| 7 | G | 2 | null | 1 |
| 7 | G | 2 | null | 2 |
| 8 | H | 3 | null | 1 |
| 8 | H | 3 | null | 2 |
| 8 | H | 3 | null | 3 |
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
4 Replies
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
AlB genius! Thanks.
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?
