How to Add rows to a new Custom Table based on values of a column

[Anonymous]

Hi,

I'm stuck in a business case where I need to add duplicate entries in the same (or in a new table) from inputs given in other column.

Consider I have a table with this inputs:

State	Region	Days	Date
BA	NNE	3	23-Jun-21

Now I want to dynamically create a custom table which will have the date being pushed to the next day nased on the Days Column. The expected Output is:

State	Region	Days	Date
BA	NNE	3	23-Jun-21
BA	NNE	3	24-Jun-21
BA	NNE	3	25-Jun-21

As the Days shows 3, 3 rows need to be pushed to the next three days.

Is this possible? Thanks for the attention!

[wdx223_Daniel]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnJU0lHy83MFksZAbGSs61Wap2tkqBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Region = _t, Days = _t, Date = _t]),
    Custom1 = #table(Table.ColumnNames(Source),List.TransformMany(Table.ToRows(Source),each List.Transform({0..Number.From(_{2})-1},(x)=>List.FirstN(_,3)&{Date.AddDays(Date.From(_{3},"en"),x)}),(x,y)=>y))
in
    Custom1

[Anonymous]

Hey Daniel! Thanks for you quick answer!

You code was a sucess! But I could not use this on the real case. Since I'm no good at all in M, I was not able to understand and apply this code to my query.

Could you please help me again?

In the real case, I need to turn this:

Garagem	Região	Subtração	Retirada Realizada	Devolução Realizada
Araças	BA	3	08/12/2020	11/12/2020

The expected output is to increase the lines until "Retirada Realizada" became equal to "Devolução Realizada":

Garagem	Região	Subtração	Retirada Realizada	Devolução Realizada
Araças	BA	3	08/12/2020	11/12/2020
Araças	BA	3	09/12/2020	11/12/2020
Araças	BA	3	10/12/2020	11/12/2020
Araças	BA	3	11/12/2020	11/12/2020

Here's the link to have access to the file: https://1drv.ms/u/s!AmPm4HbVe6W2rmw1Pg_-yRWZwAOO?e=9GxBr7

Thanks again for the support!

[Anonymous · Accepted solution]

Hi again, Daniel!

Searching the functionalities of the code sent by you in docs.microsoft, I've found a way to do this. In this case there is no need to create a Column of the "DateDifference" 

Here it goes using the same example:

1 - First, we need to turn the date columns into Int64.Type

2 - Then, we have to create a custom column and build a list from this both columns:

3 - Expand the new column:

4 - This new column will replace the first date column. 

5 - At last, change the data type of this 2 columns and be happy!

Thanks in advance for you support!