Hello,
I've got a set of data in power query and wish to add a date column ,
I've created date tables, but these are no use as they create a table for each row, if someone know a tutorial they could point me to?
all the ones I come across are for created an actual table rather than just the column.
Richard.
Not enough information!
What does this date column need to do exactly? Please provide a mock-up of what you need.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali,
It's really just trying to get a date column added. I've attached a very (very) simple file, and in the data model in PQ I've added two date columns neither of which give what I want, but illustrate where I'm going wrong. one uses Number.From and the other List.Dates.
I just want a list of dates one date per row.
RD
Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thank you alan,
Can it be done through 'add custom column' or just in the advanced editor?
RD
You don't have to bother with the Advanced Editor. When you click on the Custom Column button, you get a pop-up dialog. All you need to do is paste this line into that box:
then click on OK.Please Login or Register to view this content.
Hope this helps.
Hi,,
No , I've got that far it's that this creates a list of dats for each row, as in the attachment, which can be expanded but then I end up with
a complete list of dates for each category . I just want one date to appear on each row.
I've tried using < List.Dates(start,Duration.Days(end-start),#duration(1,0,0,0))) >, but omitting List.Dates, but this didn't work either.
There's lots out about creating date tables but can't find anything about adding a column of dates to an existing data model.
Is it possible?
RD
OK, so can I clarify something? Is what Alan provided NOT what you want? If not, then can you please attach a workbook that shows us (manually mocked up) what it is you are aiming to achieve.
I have to confess that I am still confused by your requirements.
Is this what you want? I can explain, if it is.
AliGW on MS365 Insider (Windows) 64 bit
Column1 Column2 Column1.1 A B C A B C A B C A B C A B
Sheet: Sheet1
Last edited by AliGW; 01-10-2022 at 09:05 AM.
You've been back and gone again - I've made an effort to help you out here, so SOME feedback would be appreciated.
Hi Ali,
yes, sorry had to go out. just back, and I do appreciate your help.
Alan's solution didn't really work, but it was interest to work through.
the table you sent is essentially what I'm trying to achieve. Imagine you receive a list of data, which for arguments sake , is one row for each day of January, but it doesn't contain a date column, that is what I want to add. I have thought about creating a date table then adding an index column to both and using that as a basis for merging them together, but feel there should be a neater and more 'proper' way.
RD
It sounds like you could just add an index column starting at 0 and use that as the number of days argument in a Date.AddDays calculation.
Rory
Yep,
That's worked, thanks to you and Ali,
I'll sign it off.
RD
Yes, it is and thanks for your help.
Using Date.AddDays and index, suggested by rorya.
RD
Can you post a workbook showing this? It would be helpful. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks