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

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
5 years ago

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'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