CODEWITHSUNDEEP
powerbipowerquery
Coalsand
Coalsand

Reputation: 47

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

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.

Upvotes: 1

Views: 1359

Answers (3)

horseyride
horseyride

Reputation: 21393

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

enter image description here

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60389

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"

enter image description here

Upvotes: 1

DonkeyKongII
DonkeyKongII

Reputation: 461

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. enter image description here

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

Upvotes: 1

Related Questions