Forum Discussion
Automating one transaction row into four
Hi All -
Bit of a brain teaser for you here.
I'm building a workbook with power query and power pivot to track transactions for CY and PYs. One of the products I'm tracking is a multiday product (meaning the rev and qty should be even distributed over the four days), but the reporting I use only shows the total revenue on the first day and doesn't include the following 3-days.
I've cleaned the data in one query:
I have look-up tables with the related dates for each product:
What I need is for everytime a transaction (like in the first image) occurs, all of the data duplicates into three more lines - one for each of the subsequent product_dates as referenced in the lookup table.
Ideally - this is what I'm hoping to get for every one of these product transaction lines:
Any thoughts would be appreciated!
1 Reply
Anonymous Can you post data as text? 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.
