Forum Discussion
Split/copy row (Year, Quarter, Month)
Hi,
Need some help to figure this out....
All products in a product portfolio are valid for a certain time period, for example for the year 2020, the 1st quarter of 2020 and so on. Certain products in the product portfolio need to be split into quarterly products from the parent annual contract and from the quarterly products into monthly products. What would be the optimal solution to achieve this, preferably in Power Query?
In below example, product A-2020 is the base product (parent) and this needs to be split into 4 quarterly products and 12 monthly products.
Best regards,
Chris
| YearProduct | QuarterProduct | MonthProduct |
| A-2020 | A-Q1-2020 | A-Jan-2020 |
| A-Q1-2020 | A-Feb-2020 | |
| A-Q1-2020 | A-Mar-2020 | |
| A-Q2-2020 | A-Apr-2020 | |
| A-Q2-2020 | A-May-2020 | |
| A-Q2-2020 | A-Jun-2020 | |
| A-Q3-2020 | A-Jul-2020 | |
A-Q3-2020 | A-Aug-2020 | |
A-Q3-2020 | A-Sep-2020 | |
| A-Q4-2020 | A-Oct-2020 | |
| A-Q4-2020 | A-Nov-2020 | |
| A-Q4-2020 | A-Dec-2020 |
Anonymous
Here's a custom function I wrote, which can use either text or list arguments:
// fnGen (YearProduct)=> let fx =(str)=> let split = Text.Split(str,"-"), rec = List.Transform( {0..11}, each let lst={ str, split{0}&Number.ToText(Number.IntegerDivide(_, 3)+1, "-Q0-")&split{1}, split{0}&Date.ToText(#date(Number.From(split{1}), _+1, 1), "-MMM-yyyy", "en-US") } in Record.FromList(lst, {"YearProduct", "QuarterProduct", "MonthProduct"}) ) in rec, gen = List.Transform((if YearProduct is list then YearProduct else {YearProduct}), fx), result = Table.FromRecords(List.Combine(gen)) in resultThe way to use it is shown below:
4 Replies
- AnonymousNot applicable
let y2q2m = (prod as text, year as text)=> let q2m= List.Transform({1..4}, (q)=> [qProd= prod&"-Q"&Text.From(q)&"-"&year ,mProd= List.Transform({1..3}, each prod&"-"&Date.MonthName(#date(2020,_+(q-1)*3,1),"en-US")&"-"&year)]) in Table.FromColumns({{year},{q2m}},{"yProd","qmProd"}) in y2q2minvoking the function
let Source = y2q2m("P", "2020"), #"Expanded qmProd" = Table.ExpandListColumn(Source, "qmProd"), #"Expanded qmProd1" = Table.ExpandRecordColumn(#"Expanded qmProd", "qmProd", {"qProd", "mProd"}, {"qProd", "mProd"}), #"Expanded mProd" = Table.ExpandListColumn(#"Expanded qmProd1", "mProd") in #"Expanded mProd"get the table
- ziying35
Impactful Individual
Anonymous
Here's a custom function I wrote, which can use either text or list arguments:
// fnGen (YearProduct)=> let fx =(str)=> let split = Text.Split(str,"-"), rec = List.Transform( {0..11}, each let lst={ str, split{0}&Number.ToText(Number.IntegerDivide(_, 3)+1, "-Q0-")&split{1}, split{0}&Date.ToText(#date(Number.From(split{1}), _+1, 1), "-MMM-yyyy", "en-US") } in Record.FromList(lst, {"YearProduct", "QuarterProduct", "MonthProduct"}) ) in rec, gen = List.Transform((if YearProduct is list then YearProduct else {YearProduct}), fx), result = Table.FromRecords(List.Combine(gen)) in resultThe way to use it is shown below:
- Greg_Deckler
Community Champion
Anonymous - So what are you starting with, a list of quarterly periods and a list of monthly periods? What do those base tables look like? Are they related?
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. - CNENFRNL
Community Champion
Gurus have provided preceding solutions above. Mine is for practice of List.Generate only
let Source = Table.FromColumns({{2020..2022}, {"A".."C"}}, {"Year", "Product"}), #"Added Custom" = Table.AddColumn( Source, "Custom", (EachRow as record) => List.Skip( List.Generate( () => 0, each _ <= 12, each _ + 1, each [y = EachRow[Year], q = Date.QuarterOfYear(#date(y, _, 1)), m = Date.ToText(#date(y, _, 1), "yyyy-MMM"), str = EachRow[Product] & "-" & Text.From(y) & "-Q" & Text.From(q) & ";" & EachRow[Product] & "-" & "-" & m][str] ), 1 ) ), #"Split Column by Delimiter" = Table.SplitColumn(Table.ExpandListColumn(#"Added Custom", "Custom"), "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"YearQrt", "YearMonth"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"YearQrt", type text}, {"YearMonth", type text}}) in #"Changed Type"Year Product YearQrt YearMonth 2020 A A-2020-Q1 A-2020-Jan 2020 A A-2020-Q1 A-2020-Feb 2020 A A-2020-Q1 A-2020-Mar 2020 A A-2020-Q2 A-2020-Apr 2020 A A-2020-Q2 A-2020-May 2020 A A-2020-Q2 A-2020-Jun 2020 A A-2020-Q3 A-2020-Jul 2020 A A-2020-Q3 A-2020-Aug ...