How to Duplicate Rows and Change Values

[cpiolla]

Hi everyone!

I'm currently working on a dashboard that disposes informations about the delivery of laboratory samples at my company.

For example, the sample '2000022183' was supposed to be delivered at April 29th but it arrived only at August 15th.
What I need is to duplicate the row of that sample and add plus one month on Expected Date for the necessary times until the Month from Expected and Actual dates are the same. It needs to be done automatic by Power BI because the table for this dash has thousands of different samples registered and it's impossible to do it manually.

Is it possible to do it using Power Query or something like that???

Here is an image of what i need:

[v-jingzhang · Accepted solution]

Hi @cpiolla 

First add a custom column with below code to have a list of dates on each row. 

let startDate = Date.AddMonths(Date.StartOfMonth([Expected Date]),1), endDate = [Actual Date] in List.Combine({{[Expected Date]}, List.Generate(()=> startDate, each _ <= endDate, each Date.AddMonths(_, 1))})

Then expand above list column to new rows. You will have all expected dates in a new column. Remove the old "Expected Date" column and rename the new column. 

Full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIytDBW0lEy0Tey1DcyMDICsi30DU0h7FidaCUTI3MjE6CoMVxUR8lc3xCqIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID Sample" = _t, #"Expected Date" = _t, #"Actual Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID Sample", Int64.Type}, {"Expected Date", type date}, {"Actual Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let startDate = Date.AddMonths(Date.StartOfMonth([Expected Date]),1), endDate = [Actual Date] in List.Combine({{[Expected Date]}, List.Generate(()=> startDate, each _ <= endDate, each Date.AddMonths(_, 1))})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[cpiolla]

Thank you @v-jingzhang ! It worked perfectly!