Create extra rows based on amount but only keep some fields

[richard-powerbi]

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

[AlB · Accepted solution]

Hi @richard-powerbi 

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 

[richard-powerbi]

@AlB genius! Thanks.

[camargos88]

Hi  @richard-powerbi ,

Sorry, I didn't get what you wanna do.

Can you explain it more ?

[richard-powerbi]

@camargos88 

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?