Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pmay
Resolver I
Resolver I

Adding Rows with values for each Key

I have a SQL source of investments, with a key.  Each investment should produce monthly revenue immediately.  Simplified, I have the columns InvestmentKey, MonthSinceInvestment (this starts at 0, increments by 1 each month), RevenueAmount and ExpectedRevenueAmount.

 

Due to merger & acquisition activity, I now have to integrate data from another company in this model.  It's been loaded into SQL, but they do not always produce [RevenueAmount] or even [ExpectedRevenueAmount] immediately.  Sometimes, their first [MonthSinceInvestment] value on an [InvestmentKey] will be 4, 5, 10, whatever.

 

In DAX, I do a IRR calculation using this value, and it relies on having a month 0, and 121 consecutive months through to Month 120 in [MonthSinceInvestment] for each [InvestmentKey].  Therefore, despite the new company not generating revenue until a few months after the investment, I must have values 0 through to 120 in [MonthSinceInvestment] for each and every [InvestmentKey].  It does not matter if no revenue is generated or expected, this actually makes the DAX calculation for IRR more accurate.

 

I need to identify all the [InvestmentKeys] that are missing a sequential value between 0 & 120 in [MonthSinceInvestment], and add rows to each of those [InvestmentKeys] to ensure that I have valid data to calculate on.

 

I have so far identified all the keys which are missing month 0, but I can't even figure out how to add the missing data to that.  It's not a sufficient identification anyway.  The steps I took to achieve this are:

 

#"Sorted Rows" = Table.Buffer(Table.Sort(Source,{{"InvestmentKey", Order.Ascending}, {"MonthSinceInvestment", Order.Ascending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"InvestmentKey"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([MonthSinceInvestment] <> 0))

 

Thanks in advance for any help, I hope I've explained enough to get started.

 

Edit: I have realised that if I can simply add the 121 rows for each key, I can use GroupBy to sum them into the revenue value, it will will leave all revenue values unchanged (because the data I'm adding will have null value), but gives me the rows I need.  So really, the question is just, how to add 121 rows with values 0 to 120 for each key.  

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @pmay ,

 

Given a table with one distinct column: [Key], you can just create a new custom column like this:

= {0..120}

 

Expand this new column to new rows.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

Hi @pmay ,

 

Given a table with one distinct column: [Key], you can just create a new custom column like this:

= {0..120}

 

Expand this new column to new rows.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thank you very much.  I guessed it was simple, and I'd seen the range stuff ({0..120}) in a Curbal video, but I couldn't remember the syntax and wasn't sure if it would do exactly what I wanted.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors