Forum Discussion
Duplicate rows and create date columns
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
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 chgTypesYou can now follow the steps I took to complete this.
Pete
4 Replies
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
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
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 chgTypesYou can now follow the steps I took to complete this.
Pete