Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
slap33
Frequent Visitor

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 !

 

 

1 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

 

 

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

5 REPLIES 5
slap33
Frequent Visitor

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

 

screenshot.png

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

 

 

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
slap33
Frequent Visitor

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
Super User
Super User

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
slap33
Frequent Visitor

Thanks @KNP 

 

will do (as soon as i found how to 🙂

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.