Need a M function to get the proper date

[slap33]

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 !

[slap33]

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

[KNP · Accepted solution]

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]

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

[KNP]

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]

Thanks @KNP 

will do (as soon as i found how to 🙂