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

     

     

     

     

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