Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 PLAN...
Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
BA_Pete
Icon for Super User rankSuper User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    BA_Pete 

     

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

     

    Have a good day,

    Cado