Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table like that : Customer|Date|amount
I need to duplicate each row 12 times each time with the next month
example:
original row
customer | Date | amount
1 | 1/1/17 | 100
I need it to be like :
customer | Date | amount
1 | 1/1/17 | 100
1 | 1/2/17 | 100
1 | 1/3/17 | 100
1 | 1/4/17 | 100
Is it possible to do it with power bi?
Thanks,
Solved! Go to Solution.
Hi @Drors
Try this solution
Go to Modelling Tab>>> New Table
New Table = GENERATE ( TableName, VAR mymonth = MONTH ( TableName[Date] ) RETURN GENERATESERIES ( 0, 11, 1 ) )
Now Add this CALCULATED COLUMN to your NEW TABLE
New date = DATE ( YEAR ( 'New Table'[Date] ), MONTH ( 'New Table'[Date] ) + 'New Table'[Value], DAY ( 'New Table'[Date] ) )
Hi @Drors
Try this solution
Go to Modelling Tab>>> New Table
New Table = GENERATE ( TableName, VAR mymonth = MONTH ( TableName[Date] ) RETURN GENERATESERIES ( 0, 11, 1 ) )
Now Add this CALCULATED COLUMN to your NEW TABLE
New date = DATE ( YEAR ( 'New Table'[Date] ), MONTH ( 'New Table'[Date] ) + 'New Table'[Value], DAY ( 'New Table'[Date] ) )
how about if i need it dublicated until todays month? i dont need it dublicated only 11 times!
thanks,
HI @Drors
(With your sample data)
I added one more row for testing
wow thank you it works!
Do you have any idea how to do it with a regular table and not calculated one, I think it will make my work difficault later..
and again, Thank you very much, I work on it all day
Hi @Drors
I will look into Power Query solution
@MarcelBeugcould you help?
This would be my Power Query solution:
let Source = TableName, DateList = Table.TransformColumns(Source,{{"Date", (startdate) => List.Transform({0..11}, each Date.AddMonths(startdate,_)), type {date}}}), #"Expanded Date" = Table.ExpandListColumn(DateList, "Date") in #"Expanded Date"
Hi @MarcelBeug , superb solution.
Is there a chance to change number of new rows dynamicly based on value from column, differently for every row? So the same rows needs to be duplicated 2 and some 12 times depending what value is in columnt QTY in row that is duplicated. Can I ask for an example in m-language?
2nd question is about changing values in 2 kolumns for each duplicated row. How to change date adding 1 month (as in the example) and in the same time add 1 day for date_2.
Thank you in advance
BR
Pawel
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |