Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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 !
Solved! Go to 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 ;). |
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. | Proud to be a Super User! |
sorry it's a screenshot but the sample file is really simple :
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 ;). |
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. | Proud to be a Super User! |
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
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 ;). |
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. | Proud to be a Super User! |
User | Count |
---|---|
25 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
44 | |
27 | |
21 | |
16 | |
12 |