Duplicate rows and create date columns

[Anonymous]

Hi the community !

I need your help for a trick.

To explain it quickly and simply, below is what I have and what I want in the query editor :

Starting point :

LOCATION

PLANT A

PLANT B

Expected result :

LOCATION	START_DATE	END_DATE
PLANT A	2021-01-01	2021-01-31
PLANT A	2021-02-01	2021-02-28
...	...	...
PLANT A	2021-12-01	2021-12-31
PLANT B	2021-01-01	2021-01-31
...	...	...
PLANT B	2021-12-01	2021-12-31

A first solution I imagined is to create one column for every date and then unpivot it but it's not efficient at all.

Does someone knows a nice trick to do it easily and with more class ?

Best regards 

Cado

[BA_Pete]

Hi @Anonymous ,

This is relatively easy to do I think, but I need to know where your dates/date ranges are coming from?

*EDIT* It looks like it's just Start of January | End of January, Start of Februaruy | End of February for each PLANT. Is that correct?

Pete

[Anonymous]

Hi @BA_Pete 

The START_DATE column corresponds to the first day of every month in the year, the END_DATE column corresponds to the last day of every month in the year.

The first month is january 2021 and the last month can be the current one if possible.

Regards,

Cado

[BA_Pete · Accepted solution]

@Anonymous ,

No problem.

In Power Query, create a new blank query then paste this over the default code in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvBx9AtRcFSK1YGxnZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOCATION = _t]),
    addSTART_DATE = Table.AddColumn(Source, "START_DATE", each List.Distinct(List.Transform({Number.From(Date.StartOfYear(DateTime.LocalNow()))..Number.From(Date.From(DateTime.LocalNow()))}, each Date.StartOfMonth(Date.From(_))))),
    expandSTART_DATE = Table.ExpandListColumn(addSTART_DATE, "START_DATE"),
    addEND_DATE = Table.AddColumn(expandSTART_DATE, "END_DATE", each Date.EndOfMonth([START_DATE])),
    chgTypes = Table.TransformColumnTypes(addEND_DATE,{{"START_DATE", type date}, {"END_DATE", type date}})
in
    chgTypes

You can now follow the steps I took to complete this.

Pete

[Anonymous]

@BA_Pete 

Thank you very much, I managed adapting your code to my query and it works perfectly !

Have a good day,

Cado