Query to return multiple rows based on the value in a column

Question

I have a source table that has projects with a start date and duration in months. I'm looking to write a PowerQuery for PowerBI that will create a row for each month of the project, counting up the months. For example:

Source:
Project(string) | Date (ms timestamp)    | Duration (integer)
A               | Jan-2022               | 3
B               | Sep-2022               | 2
    
Result:
Project | Date
A       | Jan-2022
A       | Feb-2022
A       | Mar-2022
B       | Sep-2022
B       | Oct-2022

Not sure where to start or what this query should look like. Any ideas?

Edit: Changed sample tables to make them readable

Edit: Dates in the source table are provided in millisecond timestamp format (eg 1641024000000). My intent in the result table is to have them in a human-readable date format.

Hopefully your date column really is a DATE/DATETIME based column and not just a formatted string. Start with that. Edit post and clarify date column context. Formatting the output to show month/year is easy. Then, edit your post tags. Which SQL, SQL-Server, Oracle, MySQL, etc. — DRapp
– DRapp, Commented Mar 9, 2022 at 0:03
Please add details of the table schema / data types, the actual date values you're working with and TAG your RDBMS — Stu
– Stu, Commented Mar 9, 2022 at 0:11
The date values in my source table are actually in millisecond timestamp format. So, for Jan 1, 2022, it's 1641024000000. As for which SQL, I should clarify that I'm doing this on PowerBI which is PowerQuery (which I'm entirely new to). I'd just added SQL as I figured an SQL-oriented solution could get the approach across. The source table is coming from a Hubspot API, and it's a single table. — Coalsand
– Coalsand, Commented Mar 9, 2022 at 1:03

Ron Rosenfeld · Accepted Answer · 2022-03-09 02:20:43Z

Here is one way to do this in Power Query.

Paste the code into a blank query.
Then Change the Source line so as to load your actual data table.
- I used an Excel table for the source, but you may use what ever.
- I also have the unix time stamp in the Source table, converting it to a PQ date in the M Code.
- If all of your time stamps do not equate to the start of the month, some additional logic may be required.
Read the code comments and explore the Applied Steps to understand the algorithm

let

//Read in the Source data
    Source = Excel.CurrentWorkbook(){[Name="Table27"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {" Date", Int64.Type}, {" Duration", Int64.Type}}),

//convert date from unixTime in milliseconds to a PQ date
    unixTime = Table.TransformColumns(#"Changed Type",{" Date", each #duration(0,0,0,_/1000)+#date(1970,1,1)}),

//add custom column with a List of the desired dates
    #"Added Custom" = Table.AddColumn(unixTime, "Months", each 
        List.Accumulate(
            {0..[#" Duration"]-1},
            {},
            (state,current)=> state & {Date.AddMonths([#" Date"],current)})),

//Remove unneeded columns
//Expand the list and set the data thype
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{" Date", " Duration"}),
    #"Expanded Months" = Table.ExpandListColumn(#"Removed Columns", "Months"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Months",{{"Months", type date}})
in
    #"Changed Type1"

DonkeyKongII · Accepted Answer · 2022-03-09 01:11:44Z

For some reason sqlfiddle was down for me so I made an example in db-fiddle using postgres instead of ms-sql.

What you're looking to accomplish can be done with a recursive CTE, the syntax in MS-SQL is slightly different but this should get you most of the way there.

WITH RECURSIVE project_dates AS(
  SELECT 
      start_date as starting_date,
      CAST(start_date + duration*INTERVAL '1 month' as date) as end_date,
      project
  FROM projects
  UNION
    SELECT 
        CAST(starting_date + INTERVAL '1 month' as date),
        pd.end_date,
        p.project
    FROM projects p
        JOIN project_dates pd ON pd.project = p.project 
    WHERE CAST(starting_date + INTERVAL '1 month' as date) < pd.end_date
) 
SELECT starting_date, project FROM project_dates
ORDER BY project, starting_date

My results using your date look as such.

You can check out my answer on db-fiddle with this link: https://www.db-fiddle.com/f/iS7uWFGwiMbEmFtNmhsiWt/0

horseyride · Accepted Answer · 2022-03-09 02:44:30Z

try below

Divide your milliseconds by 86400000 and add that to 1/1/1970 to get date

Create an array based on Duration, expand to rows, add that to the start date

Remove extra columns

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ConvertToDays = Table.TransformColumns(Source,{{"Date", each Number.RoundDown(Number.From(_) / 86400000)}}),
#"Added Custom" = Table.AddColumn(ConvertToDays, "Custom", each Date.AddDays(#date(1970,1,1),18993)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Numbers(0,[Duration])),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
#"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Custom.2", each Date.AddMonths([Custom],[Custom.1]), type date),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Date", "Duration", "Custom", "Custom.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Date"}}),
TextDate = Table.AddColumn(#"Renamed Columns", "TextDate", each Date.ToText([Date],"MMM-yy"))
in  TextDate

Collectives™ on Stack Overflow

Query to return multiple rows based on the value in a column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related