Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Query / Add Column Formula to expand cost per unit and number of units by duration

Hi guys

 

Losing my head here ... and probably dead simple to create a formula for an added column.

 

Here's what I need to do ... create a profile of benefits over months - unpivoted in PowerBI so I can connect to my time intelligence calendar table:-

 

Source Data

 1) Monthly Benefit = $100 per month

 2) Benefit Duration = 36 months

 3) Benefit Start = Date

 

In Power Query Editor ... I need to traverse by the number of durations ... and create a row per month ... starting at the start date .  so I have granular benefits?

 

Does that make sense ... any help appreciated from this awesome community.

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    Try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDSNzIwMlTSUbIAYkMDA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [benStart = _t, benDuration = _t, benMonthValue = _t]),
    chgSourceTypes = Table.TransformColumnTypes(Source,{{"benStart", type date}, {"benDuration", Int64.Type}, {"benMonthValue", Int64.Type}}),
    addBenLastDate = Table.AddColumn(chgSourceTypes, "benLastDate", each Date.AddMonths([benStart], [benDuration] - 1), type date),
    addBenDateList = Table.AddColumn(addBenLastDate, "benDateList", each List.Transform({Number.From([benStart])..Number.From([benLastDate])}, each Date.From(_))),
    expandBenDateList = Table.ExpandListColumn(addBenDateList, "benDateList"),
    filterDayMatch = Table.SelectRows(expandBenDateList, each (Date.Day([benStart]) = Date.Day([benDateList])))
in
    filterDayMatch

     

    Summary:

    1) Get the date of the last benefit payment (start + duration - 1)

    2) Create list of dates between start and end dates

    3) Filter rows where day element of date matches between start date and date list.

     

    I've left it in steps so you can follow the process, but could easily be combined into one or two steps if you wanted to.

     

    Pete

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous Seems like you would need to use something like List.Generate to do what you want. In DAX you would use GENERATESERIES. Not entirely sure of your source data or expected output though. Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    Try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDSNzIwMlTSUbIAYkMDA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [benStart = _t, benDuration = _t, benMonthValue = _t]),
    chgSourceTypes = Table.TransformColumnTypes(Source,{{"benStart", type date}, {"benDuration", Int64.Type}, {"benMonthValue", Int64.Type}}),
    addBenLastDate = Table.AddColumn(chgSourceTypes, "benLastDate", each Date.AddMonths([benStart], [benDuration] - 1), type date),
    addBenDateList = Table.AddColumn(addBenLastDate, "benDateList", each List.Transform({Number.From([benStart])..Number.From([benLastDate])}, each Date.From(_))),
    expandBenDateList = Table.ExpandListColumn(addBenDateList, "benDateList"),
    filterDayMatch = Table.SelectRows(expandBenDateList, each (Date.Day([benStart]) = Date.Day([benDateList])))
in
    filterDayMatch

     

    Summary:

    1) Get the date of the last benefit payment (start + duration - 1)

    2) Create list of dates between start and end dates

    3) Filter rows where day element of date matches between start date and date list.

     

    I've left it in steps so you can follow the process, but could easily be combined into one or two steps if you wanted to.

     

    Pete