+ Reply to Thread
Results 1 to 15 of 15

Adding a column of dates to an existing data model in power query

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    162

    Adding a column of dates to an existing data model in power query

    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.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Adding a column of dates to an existing data model in power query

    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" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    162

    Re: Adding a column of dates to an existing data model in power query

    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
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,484

    Re: Adding a column of dates to an existing data model in power query

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    162

    Re: Adding a column of dates to an existing data model in power query

    Thank you alan,
    Can it be done through 'add custom column' or just in the advanced editor?

    RD

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Adding a column of dates to an existing data model in power query

    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:

    Please Login or Register  to view this content.
    then click on OK.

    Hope this helps.

  7. #7
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    162

    Re: Adding a column of dates to an existing data model in power query

    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

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Adding a column of dates to an existing data model in power query

    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.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Adding a column of dates to an existing data model in power query

    Is this what you want? I can explain, if it is.

    AliGW on MS365 Insider (Windows) 64 bit

    H
    I
    J
    1
    Column1 Column2 Column1.1
    2
    A
    1
    01/01/2000
    3
    B
    2
    02/01/2000
    4
    C
    3
    03/01/2000
    5
    A
    4
    04/01/2000
    6
    B
    5
    05/01/2000
    7
    C
    6
    06/01/2000
    8
    A
    7
    07/01/2000
    9
    B
    8
    08/01/2000
    10
    C
    9
    09/01/2000
    11
    A
    10
    10/01/2000
    12
    B
    11
    11/01/2000
    13
    C
    12
    12/01/2000
    14
    A
    13
    13/01/2000
    15
    B
    14
    14/01/2000
    Sheet: Sheet1
    Attached Files Attached Files
    Last edited by AliGW; 01-10-2022 at 09:05 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Adding a column of dates to an existing data model in power query

    You've been back and gone again - I've made an effort to help you out here, so SOME feedback would be appreciated.

  11. #11
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    162

    Re: Adding a column of dates to an existing data model in power query

    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

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,230

    Re: Adding a column of dates to an existing data model in power query

    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

  13. #13
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    162

    Re: Adding a column of dates to an existing data model in power query

    Yep,
    That's worked, thanks to you and Ali,
    I'll sign it off.

    RD

  14. #14
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    162

    Re: Adding a column of dates to an existing data model in power query

    Yes, it is and thanks for your help.
    Using Date.AddDays and index, suggested by rorya.

    RD

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,540

    Re: Adding a column of dates to an existing data model in power query

    Can you post a workbook showing this? It would be helpful. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Can not group after adding tables in Power Pivot data model
    By lastnn30 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-24-2021, 05:30 PM
  2. [SOLVED] Adding/Updating Data to an existing Power Query
    By LisaWilliams231 in forum Excel General
    Replies: 5
    Last Post: 05-03-2020, 12:41 PM
  3. Can't add power query to data model
    By yansong888 in forum Excel General
    Replies: 1
    Last Post: 11-14-2019, 09:18 PM
  4. Power Query makes Excel crash - can't access Data Model
    By phil34 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2018, 01:01 PM
  5. Power Query and adding a column changing dates
    By ammartino44 in forum Excel General
    Replies: 0
    Last Post: 01-09-2018, 05:24 AM
  6. Replies: 7
    Last Post: 07-25-2017, 08:38 AM
  7. adding a calculated field to power pivot data model
    By stephme55 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-26-2016, 07:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1