Forum Discussion

slap33's avatar
slap33
Helper I
3 years ago
Solved

Need a M function to get the proper date

Hi, my problem is simple

 

i've got excel files to import and append.

each one of them have a date creation (excel metadata)

and inside these files i've got an array with day names in column...

 

Monday | Thuesday | Wedn....

 

My powerquery pivot those columns in an attribute/values table so i have the day names in rows.

 

And i want to get the date of each days :

    - if my creation date is the 20/10/2022

    - i know this is a thurday

    - and so my [Date] field will be as follows :

 

Day_name | Date

monday | 17/10/22

tuesday | 18/10/22

wednesday | 19/10/22

thursday | 20/10/22

friday | 21/10/22

 

Hope this is clear enough, thanks for your help !

 

 

Need Help
  • KNP's avatar
    KNP
    3 years ago

    Makes sense, I would probably approach it slightly differently.

    Here's the code, paste this into the advanced editor, alternately, Excel file is attached.

     

    let
    Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
    CT1 = Table.TransformColumnTypes(Source, {{"Column1", type date}}),
    Rename1 = Table.RenameColumns(CT1, {{"Column1", "Date"}}),
    InsertStartOfWeek = Table.AddColumn(
        Rename1, "Start of Week", each Date.StartOfWeek([Date]), type date
    ),
    InsertEndOfWeek = Table.AddColumn(
        InsertStartOfWeek, "End of Week", each Date.EndOfWeek([Date]), type date
    ),
    AddDateList = Table.AddColumn(
        InsertEndOfWeek, "DateList", each {Number.From([Start of Week])..Number.From([End of Week])}
    ),
    ExpandDateList = Table.ExpandListColumn(AddDateList, "DateList"),
    CT2 = Table.TransformColumnTypes(ExpandDateList, {{"DateList", type date}}),
    InsertDayName = Table.AddColumn(
        CT2, "Day Name", each Date.DayOfWeekName([DateList]), type text
    ),
    ROC1 = Table.SelectColumns(InsertDayName, {"DateList", "Day Name"}),
    Rename2 = Table.RenameColumns(ROC1, {{"DateList", "Date"}})
in
    Rename2

     

     

     

     

5 Replies

  • KNP's avatar
    KNP
    Super User
    3 years ago

    Probably best to paste some raw data of what you're starting with to make it easier for us to come up with a solution.

     

    • slap33's avatar
      slap33
      Helper I
      3 years ago

      Thanks KNP 

       

      will do (as soon as i found how to 🙂

  • slap33's avatar
    slap33
    Helper I
    3 years ago

    sorry it's a screenshot but the sample file is really simple :

     

    • KNP's avatar
      KNP
      Super User
      3 years ago

      Makes sense, I would probably approach it slightly differently.

      Here's the code, paste this into the advanced editor, alternately, Excel file is attached.

       

      let
    Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
    CT1 = Table.TransformColumnTypes(Source, {{"Column1", type date}}),
    Rename1 = Table.RenameColumns(CT1, {{"Column1", "Date"}}),
    InsertStartOfWeek = Table.AddColumn(
        Rename1, "Start of Week", each Date.StartOfWeek([Date]), type date
    ),
    InsertEndOfWeek = Table.AddColumn(
        InsertStartOfWeek, "End of Week", each Date.EndOfWeek([Date]), type date
    ),
    AddDateList = Table.AddColumn(
        InsertEndOfWeek, "DateList", each {Number.From([Start of Week])..Number.From([End of Week])}
    ),
    ExpandDateList = Table.ExpandListColumn(AddDateList, "DateList"),
    CT2 = Table.TransformColumnTypes(ExpandDateList, {{"DateList", type date}}),
    InsertDayName = Table.AddColumn(
        CT2, "Day Name", each Date.DayOfWeekName([DateList]), type text
    ),
    ROC1 = Table.SelectColumns(InsertDayName, {"DateList", "Day Name"}),
    Rename2 = Table.RenameColumns(ROC1, {{"DateList", "Date"}})
in
    Rename2

       

       

       

       

      • slap33's avatar
        slap33
        Helper I
        3 years ago

        ahah just the excel attached is enough to impress me   ^^

        then the video to show the result in action --> BOUM

        thank you so much ! I check it and will accept as solution if good thankyouuuuuuu